cancel
Showing results 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

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:

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
Community Support

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:

2.Next create relationships between tables:

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.

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.

3 REPLIES 3
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!

Frequent Visitor

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?

Community Support

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:

2.Next create relationships between tables:

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.

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.