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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
shanestocks
Frequent Visitor

Rolling Total 12 Month - Number of Accidents

Hi, I am trying to get a rolling 12-month total based on the date of the graph, but for some reason the totals are going downwards. I am not sure if it is displaying correctly but I am pretty sure the DAX is correct. All I want to do is total the number of accidents that have occurred by period and I am getting the following visual:

 

shanestocks_0-1717495379059.png

Below is the number of accidents (1 at a time), and the line above is supposed to be the rolling 12-month total, but just confused as to why it dips and not just totals?

 

The DAX I am using is: 

 

Rolling Accidents =
 
CALCULATE(
    [No. of Incidents],
    DATESINPERIOD(date_table[date], MAX(date_table[date]), -12, MONTH)
)
 
[No. of Incidents] =
 
No. of Incidents = CALCULATE(COUNTROWS(hse_records),
hse_records[sofina_accident_class]<>"NM"
)
 
Is this correct? I would expect the line to continuously move up? Also, I am not sure my date field is working as intended, it doesn't group it by month etc when I drop it in a field?
 
Thank you all.
1 ACCEPTED SOLUTION

Thanks for the reply from @rajendraongole1,, please allow me to provide another insight:
Hi,@shanestocks 

Regarding the issue you raised, my solution is as follows:

1.First I have created the following table and the column names and data are the data you have given:

vlinyulumsft_0-1717574363869.png

vlinyulumsft_2-1717574385189.png

2.Next create relationships between tables:

vlinyulumsft_3-1717574398128.png

3.Create calculated column references:

Column = LOOKUPVALUE(hse_records[sofina_accident_class],'hse_records'[Date],'date_table'[Date])

4. Below are the measure I've created for your needs:

Measure = CALCULATE(COUNTROWS('date_table'),FILTER(ALLSELECTED(date_table),'date_table'[Date]<MAX('date_table'[Date])&&'date_table'[Column]<>"NM"))

5.Here's my final result, which I hope meets your requirements.

vlinyulumsft_4-1717574461926.png

One more note: the official documentation notes that the DATESINPERIOD () function is not recommended for use in visual calculations because it may return meaningless results.

Here is the relevant documentation:

DATESINPERIOD function (DAX) - DAX | Microsoft Learn


Please find the attached pbix relevant to the case.

 

Best Regards,

Leroy Lu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

 

View solution in original post

3 REPLIES 3
rajendraongole1
Community Champion
Community Champion

Hi @shanestocks - You can create an measure counts the rows in the hse_records table where the sofina_accident_class is not equal to "NM":

 

No. of Incidents =
CALCULATE(
COUNTROWS(hse_records),
hse_records[sofina_accident_class] <> "NM"
)

 

another measure for rolling 12 months total. 

 

Rolling Accidents =
CALCULATE(
[No. of Incidents],
DATESINPERIOD(
date_table[date],
LASTDATE(date_table[date]),
-12,
MONTH
)
)

 

If the rolling totals still appear incorrect, consider checking the incident data itself for any anomalies or missing data within the 12-month periods.

 

Did I answer your question? Mark my post as a solution! This will help others on the forum!
Appreciate your Kudos!!

Thanks for response. LASTDATE returned the same graph. I believe it is correct it's just so difficult to tell. I didn't expect the numbers to go down like they do. I was hoping for a RUNNING TOTAL that didn't do this?

Thanks for the reply from @rajendraongole1,, please allow me to provide another insight:
Hi,@shanestocks 

Regarding the issue you raised, my solution is as follows:

1.First I have created the following table and the column names and data are the data you have given:

vlinyulumsft_0-1717574363869.png

vlinyulumsft_2-1717574385189.png

2.Next create relationships between tables:

vlinyulumsft_3-1717574398128.png

3.Create calculated column references:

Column = LOOKUPVALUE(hse_records[sofina_accident_class],'hse_records'[Date],'date_table'[Date])

4. Below are the measure I've created for your needs:

Measure = CALCULATE(COUNTROWS('date_table'),FILTER(ALLSELECTED(date_table),'date_table'[Date]<MAX('date_table'[Date])&&'date_table'[Column]<>"NM"))

5.Here's my final result, which I hope meets your requirements.

vlinyulumsft_4-1717574461926.png

One more note: the official documentation notes that the DATESINPERIOD () function is not recommended for use in visual calculations because it may return meaningless results.

Here is the relevant documentation:

DATESINPERIOD function (DAX) - DAX | Microsoft Learn


Please find the attached pbix relevant to the case.

 

Best Regards,

Leroy Lu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

 

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

Top Solution Authors