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