Unable to calculate 12 months moving average for the measure

[Anonymous]

As you can see from the image, I am trying to find 12 months moving average for AIFR 12 from the selected month(in the month slicer). Unfortunately, I am not able to get the desired result. 

Can anyone please help me. Please find the PIBIX here  PBIX 

Thank you

[StevenGrenier]

Hi sandesh,

I've had this exact challenge in one of my reports where I had to calculate an average for the 6 months prior to the month selected in my slicer. I've adapted my solution to fit your situation:

Moving Average AI = CALCULATE(AVERAGE([AIFR 12]), DATESINPERIOD('Calendar'[Date], SELECTEDVALUE('Calendar'[Month (Current)]), -12, MONTH))

Take note however, that this formula uses a created calendar table . One of the best methods I found to calculate things with rolling dates is to have such a calendar table:

1. Go in the modeling tab and click on "New table"

2. Type: Calendar = CALENDAR(MINX('SMS Incident';'SMS Incident'[Incident Date]); MAXX('SMS Incident';'SMS Incident'[Incident Date]))

3. Add the following column in the calendar table (I used format "mmm. yyyy", but you can use another format): 

Month (Current) = FORMAT(STARTOFMONTH('Calendar'[Date].[Date]);"mmm. yyyy") 

Then make the proper relationship between your tables: 'Calendar'[Date] with 'SMS Incident'[Incident Date].

Hope this was helpful! 🙂

[Greg_Deckler]

See if my Time Intelligence the Hard Way provides a different way of accomplishing what you are going for.

https://community.powerbi.com/t5/Quick-Measures-Gallery/Time-Intelligence-quot-The-Hard-Way-quot-TIT...

[amitchandak]

I typically use these formulae. And they work well Date table.

Rolling 12 = CALCULATE(sum(Sales[Sales Amount]),DATESINPERIOD('Date'[Date],ENDOFMONTH(Sales[Sales Date]),-12,MONTH))
Rolling 12 = CALCULATE(sum(Sales[Sales Amount]),DATESINPERIOD('Date'[Date],max(Sales[Sales Date]),-12,MONTH))  

To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s. Refer :
https://radacad.com/creating-calendar-table-in-power-bi-using-dax-functions
https://www.archerpoint.com/blog/Posts/creating-date-table-power-bi
https://www.sqlbi.com/articles/creating-a-simple-date-table-in-dax/

Appreciate your Kudos.

[Anonymous]

In fact, I did what you said, but now it each value is the sum of previous values. Could you please help me here. I want average. 

Moving Average AI = CALCULATE(AVERAGEX('Measure',[AIFR 12]),DATESINPERIOD('REF Calendar'[Date],MAX('SMS Incident'[Incident Date]),-12,MONTH))

[amitchandak]

I think [AIFR 12]) is using some time stuff, can you share the formula

The one you or this one should have worked

Moving Average AI = CALCULATE(AVERAGE([AIFR 12]),DATESINPERIOD('REF Calendar'[Date],MAX('SMS Incident'[Incident Date]),-12,MONTH))

[Anonymous]

Please find the below formulas 

Moving Average AI = CALCULATE([AIFR 12],DATESINPERIOD('REF Calendar'[Date],MAX('SMS Incident'[Incident Date]),-12,MONTH))

AIFR 12 = CALCULATE(DIVIDE(([AI]),[Net Hours],0)*1000000, DATESBETWEEN('SMS Incident'[Incident Date], EDATE([Max Ref Pick Date],-12), [Max Ref Pick Date]))

AI = CALCULATE(COUNT('SMS Incident'[Incident ID]))

Max Ref Pick Date = MAX('Ref Pick Calendar'[Date])

[dax]

Hi @Anonymous , 

You could refer to my sample for details.

Best Regards,
Zoe Zhi

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

[Ashish_Mathur]

Hi,

Show your desired result very clearly.

[Anonymous]

Hi Ashish,

I want the moving average for the measure 'AIFR 12', from the last 12 months from the selected month. 

For example in the image I have selected month 3 and I am getting the AIFR 12 for the past 12 months. But unable to get its 12MMA. 

When I applied the dax 

Moving Average AI = CALCULATE( SUMX('Measure',[AIFR 12]),DATESBETWEEN('SMS Incident'[Incident Date], EDATE([Max Ref Pick Date],-12), [Max Ref Pick Date]))

I am getting the same result as AIFR 12, instead I want moving average for AIFR 12. 

[Ashish_Mathur]

Hi,

I am confused but am willing to help.  I still do not know the exact figures that you expect to see as the end result.  Kindly illustrate the same in an Excel file.

[Anonymous]

Sorry for the late reply Ashish, it was a long weekend in Australia. 

I have tried my best to explain please find the attched doc and pbix here Document and PBIX 

[amitchandak]

@Anonymous , I checked, there is Gap in the calendar table, can you create a calendar table using calendar function and mark it as date table.

To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s. Refer :
https://radacad.com/creating-calendar-table-in-power-bi-using-dax-functions
https://www.archerpoint.com/blog/Posts/creating-date-table-power-bi
https://www.sqlbi.com/articles/creating-a-simple-date-table-in-dax/

[amitchandak]

@Anonymous , Check if measure 2 in attached(after signature) file can help

[Ashish_Mathur]

Hi,

Sorry for the delay in replying but i really cannot understand the question.

[Anonymous]

Hi Ashish,

Have you heard of the terminology called "TRIFR" Total Recorable Injuries Frequency Rate? This is very common business terminology in Australia. 

I have TRIFR, but I want to calculate TRIFR (12 MMA)  Please find the PBIX 

In the image below they have selected "Jan-20" on their date slicer and it is displaying data for the last 12 months from the "Jan-20"

I.e I need to replicate the below image in power BI. 

Hope this makes clear. 

Thank you for taking interest. Please let me know if there any other way I could help you understand the requirement?