Question Re: Time Intelligence Functions

Reefman

New Member
Joined
Aug 15, 2013
Messages
5
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
 

Some videos you may like

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
Joined
Dec 7, 2012
Messages
294
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
Joined
Aug 15, 2013
Messages
5
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.

ClaimIDTrans DatePayment
11-Jan-100.00
11-Mar-110.00
11-Dec-1210.00
11-Feb-1310.00
21-Jan-120.00
21-Mar-120.00
31-Feb-1110.00
41-May-120.00
41-Jan-135.00
51-Jan-110.00
51-Jan-1210.00
51-Jan-13-10.00

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

Transaction DateTransaction Year
1-Jan-102010
2-Jan-102010
3-Jan-102010
...


<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 1Column Labels
Row Labels201120122013
111
3111
41
511
Grand Total134

<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 2Column Labels
Row Labels201120122013
111
3111
41
51
Grand Total355

<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
2010201120122013
What I really want0133

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

Any further suggestions?
 

miguel.escobar

Active Member
Joined
Dec 7, 2012
Messages
294
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
Joined
Aug 15, 2013
Messages
5
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
 

Watch MrExcel Video

Forum statistics

Threads
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...
Top