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
Glenda
Helper I
Helper I

Claims in the last 6 Months using a dynamic date based on the current claim date

I need to count the number of claims an employee has in the last 6 month based on the date of claim, i.e if the date of injury is 31/12/20 I need to check how many claims the person has had between 30/12/20 and 30/06/20. 

 

I have a claim table that looks like this 

 

Claim NumberEmployee IDDate of Injury
QAN9224649690413/02/2020
QAN9304949690417/03/2020
QAN9359749690416/05/2020
QAN9397949690418/08/2020
QAN942814969045/10/2020
QAN9435449690415/10/2020
QAN9704949690418/12/2020
QAN9762749690427/02/2021

 

This is what I am trying to create in excel I would use COUNTIFS but I have had no joy with anything I have tried in BI. 

 

Claim NumberEmployee IDDate of InjuryInjuries in the last 6MTH
QAN9224649690413/02/20200
QAN9304949690417/03/20201
QAN9359749690416/05/20202
QAN9397949690418/08/20202
QAN942814969045/10/20203
QAN9435449690415/10/20203
QAN9704949690418/12/20203
QAN9762749690427/02/20214

 

 

I have a measure that counts the number of claims and I have the date of injury linked to the dates table. I have tried the dax below but no luck. 

6Months = CALCULATE(Claims[Claim count], DATESINPERIOD('Date'[Date],'Date'[Date],-6,MONTH))

 

here is a link to the sample data - https://we.tl/t-3EEl0Fl8Kf 

1 ACCEPTED SOLUTION

Hi,

You may download my PBI file from here.

Hope this helps.

Untitled.png


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

View solution in original post

11 REPLIES 11
Ashish_Mathur
Super User
Super User

Hi,

You may download my PBI file from here.

Hope this helps.

Untitled.png


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

Thank you so much, Ashish. This is great, I can see that my issue was including the claim number which I need to link this measure too, but I can use this to create a table that I can link back to the claim. Unless you know a better way for me to show the 6Month measure against the claim number?

 

You are welcome.  I am not clear with your next requirement.  Please elaborate.  Show me your expected result.


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

The expected results are in the post above. Basically, when a new claim comes in I need to calculate how many claims the employee has had in the last 6MTH as new claims where the worker has had 4 or more claims in the last 6months are flagged as high risk. In excel I use COUNTIF where it checks the Employee ID column and the Date of Injury column. 

 

 

=COUNTIFS(Employee_ID,B11,Date_of_Injury,"<"&C11,Date_of_Injury,">="&EDATE(C11,-6))

 

 

 

For some reason, I can't paste a table into the reply and keep getting an invalid HTML message even when I use the table button.

 

link to table in excel : https://we.tl/t-nhdNwT8sQT 

Hi,

You may download my PBI file from here.

Hope this helps.

Untitled.png


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

Thank you Ashish for your help with this. it's exactly what I needed. 

 

Thank you this is perfect. 

You are welcome.


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

@Glenda , In case you trend for the last 6 month with any date refer to this solution

https://www.youtube.com/watch?v=duMSovyosXE

 

Or use relative date slicer: https://docs.microsoft.com/en-us/power-bi/visuals/desktop-slicer-filter-date-range

 

Thanks, I actually watched this before I put the post in but it's not what I need as I need to know the information for each claim, not the date. I did manage to modify this and have an employee list instead of a date but this only allows me to look at 1 employee at a time. 

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.