# Question Re: Time Intelligence Functions

#### Reefman

##### New Member
Hi,

I'm new to Powerpivot and working my way through Rob Collie's DAX book. I've just gone through the Time Intelligence chapter and it's prompted me to look at some data I have at work.

I have an insurance claim transaction database and I want to count how many claims there are where some payment has been made, at various points in time. A claim "transactions" do not necessarily involve any payments, but, for example, could simply be logging a claim notice in the system.

I was able to count the number of unique claims by a certain date, irrespective of payments, by using the following:

[Num Claims] = DISTINCTCOUNT(transactions[ClaimID]) and
[Num Claims To Date] =
CALCULATE([Num Claims],
DATESBETWEEN(calendar[Date],
FIRSTDATE(ALL(calendar[Date])),
LASTDATE(calendar[Date])
)
)

I've also set up a cumulative paid measure as

[Paid To Date] =
CALCULATE(transactions[Payment],
DATESBETWEEN(calendar[Date],
FIRSTDATE(ALL(calendar[Date])),
LASTDATE(calendar[Date])
)
)

Is it possible to set up a measure to get the number of claims with at least one payment at given points in time? i.e., cumulative number of unique ClaimIDs where [Paid To Date] > 0 at, say, each month end?

Many thanks,

Bruno

### Excel Facts

How to find 2nd largest value in a column?
MAX finds the largest value. =LARGE(A:A,2) will find the second largest. =SMALL(A:A,3) will find the third smallest

#### miguel.escobar

##### Active Member
if it makes sense then its possible, Bruno! =)
You might want to change that DATESBETWEEN inside the Paid to Date measure to something like DATESBETWEEN( calendar[date], firstdate that you choose in the slicers, lastdate that you choose in the slicers) and that should give you the paid amount in an specific date range...then you can do something more fancy like
IF( AND( [Paid to Date] >0, [the new measure that we talked about above]>0 ), [Num of Claims], blank() )

If I didn't understand the question then what I said above will not work but let me know if it does!

#### Reefman

##### New Member
Thanks Miguel - I'm very grateful for you reply. I don't think I was 100% clear with my question, although your reply did get me to try a few more things. I think I'm getting closer!

To make it clearer, here's a small data set to illustrate what I tried.

 ClaimID Trans Date Payment 1 1-Jan-10 0.00 1 1-Mar-11 0.00 1 1-Dec-12 10.00 1 1-Feb-13 10.00 2 1-Jan-12 0.00 2 1-Mar-12 0.00 3 1-Feb-11 10.00 4 1-May-12 0.00 4 1-Jan-13 5.00 5 1-Jan-11 0.00 5 1-Jan-12 10.00 5 1-Jan-13 -10.00

<colgroup><col><col><col></colgroup><tbody>
</tbody>
and a Calendar table:

 Transaction Date Transaction Year 1-Jan-10 2010 2-Jan-10 2010 3-Jan-10 2010 ...

<colgroup><col width="64" span="2" style="width: 48pt;"></colgroup><tbody>
</tbody>
I've tried the following measure and added ClaimID in the row label to help me understand what's going on, although I'm only interested in the totals:

[Num Claims with Payments To Date Method 1] =
CALCULATE([Num Claims to Date], DATESBETWEEN(Calendar[Date], FIRSTDATE(ALL(Calendar[Date])), LASTDATE(Calendar[Date])), Transactions[Payment]>0)

Which give me the following (with Year from the Calendar table as a row heading)

 Num Claims with Payments To Date Method 1 Column Labels Row Labels 2011 2012 2013 1 1 1 3 1 1 1 4 1 5 1 1 Grand Total 1 3 4

<colgroup><col><col><col span="2"></colgroup><tbody>
</tbody>

This is almost what I want - the only issue is that I'd like to get rid of ClaimID=5 in 2013 as the total paid is 0 because of the -10 payment 1/1/2013. ie, I'm picking up claims that had any payment>0 up to the row label date, instead of claims that have cumulative paid > 0 at the row label date.

[Num Claims with Payments To Date Method 2] = if([Paid to Date]>0,[Num Claims to Date],BLANK())

Which gives me:

 Num Claims to Date With Payments Method 2 Column Labels Row Labels 2011 2012 2013 1 1 1 3 1 1 1 4 1 5 1 Grand Total 3 5 5

<colgroup><col><col><col span="2"></colgroup><tbody>
</tbody>
The problem I get with this measure is that it works for an individual ClaimID, but the totals don't work as I need.

Here's what I really would like to get:

 Column Labels 2010 2011 2012 2013 What I really want 0 1 3 3

<colgroup><col><col><col span="3"></colgroup><tbody>
</tbody>

Any further suggestions?

#### miguel.escobar

##### Active Member
can you send me a copy of your workbook via dropbox or skydrive so I can take a look? I think I'm clear now about what you want

#### Reefman

##### New Member
In case someone gets this far... Miguel (thanks!) was able to solve this problem for me offline.

The solution was to

1) create a new table called 'Claims' with unique claim id/numbers

 ClaimID 1 2 3 4 5

<colgroup><col></colgroup><tbody>
</tbody>

2) Create a new relationship where this new table is a lookup table for ClaimID in the Transactions table.

3) Define a new measure =COUNTX( FILTER(Claims, [Paid to Date]>0),Claims[ClaimID])

Cheers,

Bruno

1,102,778
Messages
5,488,814
Members
407,658
Latest member
Arias610

### This Week's Hot Topics

• Timer in VBA - Stop, Start, Pause and Reset
[CODE=vba][/CODE] Option Explicit Dim CmdStop As Boolean Dim Paused As Boolean Dim Start Dim TimerValue As Date Dim pausedTime As Date Sub...
• how to updates multiple rows in muliselect listbox
Hello everyone. I need help with below code. code is only chaning 1st row in mulitiselect list box. i know issue with code...
• Delete Row from Table
I am trying to delete a row from a table using VBA using a named range to find what I need to delete. My Range is finding the right cell. In the...
• Assigning to a variable
I have a for each block where I want to assign the value in column 5 of the found row to the variable Serv. [CODE=vba] For Each ws In...
• Way to verify information
Hi All, I don't know what to call this formula, and therefore can't search. I have a spreadsheet with information I want to reference...
• Active Cell Address – Inactive Sheet
How to use VBA to get the cell address of the active cell in an inactive worksheet and then place that cell address in a location on the current...