Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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
Solved! Go to Solution.
Hi,
You may download my PBI file from here.
Hope this helps.
Hi,
You may download my PBI file from here.
Hope this helps.
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.
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.
Thank you Ashish for your help with this. it's exactly what I needed.
Thank you this is perfect.
You are welcome.
@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.
User | Count |
---|---|
140 | |
113 | |
104 | |
77 | |
63 |
User | Count |
---|---|
135 | |
126 | |
110 | |
70 | |
61 |