5 year Average across stacked column

[Ikay]

Hi,
I’m struggling to calculate a 5yr Moving average. or even fixed average like it is used in the visual (similar to line and stacked column) attached.
It feels like an easy solution am missing somehow.

I have total Audit in the measure used in the column chat, but would want a line of 5 year moving average going across it.

It should be moving such that with a year selected, it goes back 5 years to calculate the average

Attached PBIX FILE.fire audits Annon.pbix (227.7 KB)

Please point me in the right direction

Thank you

[alexbadiu]

Hello @Ikay,

if you want to create time intelligence measures you first need a relationship between your Calendar Table and Fire Audits.
Before concentrating on the final result, you need to understand very well your data. From what I saw, you are trying to calculate the value of a state for a selected year and the value fro the year prior.
in your data, you only have the information for the state for a one year only.
In order to check your data, my recommendation is to always start with putting the data into tables.

It will allow to see how is the data structured. Then you can look at the values

[Ikay]

@alexbadiu, Hi, thank you so much,

In my haste to anonymize the data, I didn’t check it properly.
Please find attached the updated PBIX file. fire audits Annon.pbix (223.9 KB)

[alexbadiu]

@Ikay, I created the following measure

Moving AVERAGE 5 Y = 
AVERAGEX(
    DATESINPERIOD( 'Date Table'[Date] ,
    LASTDATE( 'Date Table'[Date] ),
    -5,
    YEAR
),
[Total Audits]) 

I duplicated your first page to test the numbers.
I focused only on AVON:
First I checked the Total Audits LY, then my moving average formula. Looks fine

I went back to the main page and changed the chart to Line and Clustered column chart
I added the moving average on Line values
I went to format and made the modifications below

now the chart looks like this

Please find below the pbix
fire audits Annon (1).pbix (229.2 KB)

[BrianJ]

@alexbadiu,

Even your forum solution visuals look great…

• Brian

[Ikay]

Thank you so much!..