Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
I manage a large(ish) data set of insurance claims which contain a 'received datetime' column and a 'processed datetime' column. I also have a full 'date' table in place. a claim is considered inventory between these two dates/times.
I would like to be able to count the number of claim in inventory on any given date or date range without creating additional inventory/date tables.
Ideas?
Solved! Go to Solution.
Hi @jrob ,
I think it can be done this way:
I assume you have a date table in the model, so you should be able to select a data range.
A measure like this could calculate what you are looking for.
CountDistinctClaims =
var StartDate = FIRSTDATE('Dates'[Date])
var EndDate = LastDate('Dates'[Date])
var ReturnValue = CALCULATE(
DISTINCTCOUNT('Claim'[claim ]),
filter(Claim, StartDate <= 'Claim'[out].[Date] && EndDate >= 'Claim'[in].[Date]))
return
ReturnValue
I called the 'received datetime' column and a 'processed datetime' 'claim'[in] and 'claim'[out] respectively.
the format of the dates in the filter depends on the precise definition of you date fields in your model.
Hope this helps.
Jan
Hi @jrob,
Based on JustJan's reply,as you already have a full 'date' table , a 'received datetime' column and a 'processed datetime' column,you can simply use the following measure to calculate the number of issues during the period:
CountDistinctClaims =
var StartDate = FIRSTDATE('Dates'[Date])
var EndDate = LastDate('Dates'[Date])
var ReturnValue = CALCULATE(
DISTINCTCOUNT('Claim'[claim ]),
filter(Claim, EndDate <= 'Claim'[ProcessedDate] && StartDate >= 'Claim'[ReceivedDate]))
return
ReturnValue
In above,Date's[Date] refers to the date column in a full date table.
Hope this would help.
Best Regards,
Kelly
@JustJan Thank you for your solution and apologies for the delay in responding. I am going to leave your response as the solution but the truth is I had to modify it in order to account for all claims that were active during the dynamic date range. that includes:
Inventory2 =
var StartDate = FIRSTDATE('Date'[ShortDate])
var EndDate = LASTDATE('Date'[ShortDate])
var ReturnDate =
CALCULATE(DISTINCTCOUNT(ClaimsRaw[Claim_Number]),FILTER(ClaimsRaw, ClaimsRaw[ReceivedShortDate] >= StartDate && ClaimsRaw[ProcessedShortDate] <= EndDate && NOT(ISBLANK(ClaimsRaw[Processing_Date]))))
+CALCULATE(DISTINCTCOUNT(ClaimsRaw[Claim_Number]),FILTER(ClaimsRaw, ClaimsRaw[ReceivedShortDate] < StartDate && ClaimsRaw[ProcessedShortDate] <= EndDate && ClaimsRaw[ProcessedShortDate] >=StartDate && NOT(ISBLANK(ClaimsRaw[Processing_Date]))))
+CALCULATE(DISTINCTCOUNT(ClaimsRaw[Claim_Number]),FILTER(ClaimsRaw, ClaimsRaw[ReceivedShortDate] >= StartDate && ClaimsRaw[ReceivedShortDate] <= EndDate && ClaimsRaw[ProcessedShortDate] > EndDate && NOT(ISBLANK(ClaimsRaw[Processing_Date]))))
+CALCULATE(DISTINCTCOUNT(ClaimsRaw[Claim_Number]),FILTER(ClaimsRaw, ClaimsRaw[ReceivedShortDate] < StartDate && ClaimsRaw[ProcessedShortDate] > EndDate && NOT(ISBLANK(ClaimsRaw[Processing_Date]))))
+CALCULATE(COUNTBLANK(ClaimsRaw[ProcessedShortDate]),filter(ClaimsRaw,ClaimsRaw[ReceivedShortDate] <= EndDate ))
return
ReturnDate
Hi @jrob ,
I think it can be done this way:
I assume you have a date table in the model, so you should be able to select a data range.
A measure like this could calculate what you are looking for.
CountDistinctClaims =
var StartDate = FIRSTDATE('Dates'[Date])
var EndDate = LastDate('Dates'[Date])
var ReturnValue = CALCULATE(
DISTINCTCOUNT('Claim'[claim ]),
filter(Claim, StartDate <= 'Claim'[out].[Date] && EndDate >= 'Claim'[in].[Date]))
return
ReturnValue
I called the 'received datetime' column and a 'processed datetime' 'claim'[in] and 'claim'[out] respectively.
the format of the dates in the filter depends on the precise definition of you date fields in your model.
Hope this helps.
Jan
Hi,
Share some data and show the expected result.
Check if your need is very similar to what I posted on blog. If not share some sample data with expected results
Appreciate your Kudos. In case, this is the solution you are looking for, mark it as the Solution. In case it does not help, please provide additional information and mark me with @
Thanks. My Recent Blog -
https://community.powerbi.com/t5/Community-Blog/Winner-Topper-on-Map-How-to-Color-States-on-a-Map-wi...
https://community.powerbi.com/t5/Community-Blog/Power-BI-Working-with-Non-Standard-Time-Periods/ba-p...
https://community.powerbi.com/t5/Community-Blog/Comparing-Data-Across-Date-Ranges/ba-p/823601
User | Count |
---|---|
139 | |
113 | |
103 | |
77 | |
63 |
User | Count |
---|---|
135 | |
126 | |
110 | |
70 | |
61 |