Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
jrob
Frequent Visitor

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

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?

1 ACCEPTED SOLUTION
JustJan
Responsive Resident
Responsive Resident

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 

View solution in original post

5 REPLIES 5
v-kelly-msft
Community Support
Community Support

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:

 

  • 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

 

 

JustJan
Responsive Resident
Responsive Resident

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

Hi,

Share some data and show the expected result.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
amitchandak
Super User
Super User

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

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.