DAX Formula for Dynamic Inventory of Claims with Received and Processed Dates

[jrob]

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?

[v-kelly-msft]

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

[jrob]

@Anonymous 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:

• Claims that start and end within the date range
• Claims that started before the date range and end within the date range
• Claims that started in the date range and end after the date range
• Claims that started before the date range and end after the date range
• Claims that have a start date and no end date

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

[Anonymous · Accepted 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 

[Ashish_Mathur]

Hi,

Share some data and show the expected result.

[amitchandak]

Check if your need is very similar to what I posted on blog. If not share some sample data  with expected results

https://community.powerbi.com/t5/Community-Blog/HR-Analytics-Active-Employee-Hire-and-Termination-tr...

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