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

[Glenda]

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 Number	Employee ID	Date of Injury
QAN92246	496904	13/02/2020
QAN93049	496904	17/03/2020
QAN93597	496904	16/05/2020
QAN93979	496904	18/08/2020
QAN94281	496904	5/10/2020
QAN94354	496904	15/10/2020
QAN97049	496904	18/12/2020
QAN97627	496904	27/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 Number	Employee ID	Date of Injury	Injuries in the last 6MTH
QAN92246	496904	13/02/2020	0
QAN93049	496904	17/03/2020	1
QAN93597	496904	16/05/2020	2
QAN93979	496904	18/08/2020	2
QAN94281	496904	5/10/2020	3
QAN94354	496904	15/10/2020	3
QAN97049	496904	18/12/2020	3
QAN97627	496904	27/02/2021	4

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 

[Ashish_Mathur]

Hi,

You may download my PBI file from here.

Hope this helps.

[Glenda]

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?

[Ashish_Mathur]

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

[Glenda]

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))

[Glenda]

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 

[Ashish_Mathur · Accepted solution]

Hi,

You may download my PBI file from here.

Hope this helps.

[Glenda]

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

[Glenda]

Thank you this is perfect. 

[Ashish_Mathur]

You are welcome.

[amitchandak]

@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

[Glenda]

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.