cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
jrob Frequent Visitor
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

Accepted Solutions
JustJan Member
Member

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

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

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

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





Did I answer your question? Mark my post as a solution!
In case it does not help, please provide additional information and mark me with @
My Recent Blogs -Decoding Direct Query - Time Intelligence, Winner Coloring on MAP,
HR Analytics, Power BI Working with Non-Standard TimeAnd Comparing Data Across Date Ranges
Proud to be a Super User! Linkedin


Super User IV
Super User IV

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

Hi,

Share some data and show the expected result.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
JustJan Member
Member

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

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

Microsoft v-kelly-msft
Microsoft

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

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

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

@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

 

 

Helpful resources

Announcements
Meet the 2020 Season 1 Power BI Super Users!

Meet the 2020 Season 1 Power BI Super Users!

It’s the start of a new Super User season! Learn all about the new Super Users and brand-new tiered recognition system.

Super User Challenge: Can You Solve These?

Super User Challenge: Can You Solve These?

We're celebrating the start of the New Super User season with our first ever Super User 'Can You Solve These?' challenge.

Power BI Desktop Update - February 2020

Power BI Desktop Update - February 2020

We are super excited for our update this month, as we are releasing two of our top community requests!

Power Platform Online Conference

Power Platform Online Conference

Join us for the first ever Power Platform Online Conference!

Top Solution Authors