Question Re: Time Intelligence Functions

[Reefman]

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

 

[miguel.escobar]

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]

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.

I've also used your suggestion:

[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]

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]

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