Issues with a previous 5 year average measure

[gc27]

Have struggled with this for ages so really could do with some advice;

I am trying to create a measure to report the prevous 5 year average. I have tried different measure combinations with parallel period, same period last year, etc but it doesnt work in one way or another with either monthly aggregation working but yearly not working or a fiscal year to date slicer not affecting the data when in a table.

What I am trying to get to is to create a table with the below headings using measures

*Sum column in the deliberate fires table is a column of value 1 for each row. I use this for the calculations, but maybe count rows better?

*Fiscal Year in calendar table is text format with format like 2024/25.

*I have a fiscal year to date measure in my calendar table which when toggled to FYTD limits current/previous years to dates between 1st April and todays day of year. I would like to be able to toggle this on and off to view the measures against yearly totals and just for fiscal year to date.

At present, my measures are as follows;

Slicer current year selection =if(isblank(Calculate (SUM('Deliberate Fires'[Sum]))),0,Calculate(SUM('Deliberate Fires'[Sum])))

Slicer Previous year selection =if(isblank(Calculate(SUM('Deliberate Fires'[Sum]),SAMEPERIODLASTYEAR('Calendar'[Date]))),0,Calculate(SUM('Deliberate Fires'[Sum]),SAMEPERIODLASTYEAR('Calendar'[Date])))

Slicer 1 year difference = 'Deliberate Fires'[Slicer Deliberate Fires Sum] - [Slicer Previous year selection]

Slicer 1 year % difference =

if(iserror([Slicer 1 year difference] / [Slicer Previous year selection]),

0,

[Slicer 1 year difference] / [Slicer Previous year selection])

Previous5yearaverage

*The issue with my current previous 5 year average is that is when my fiscal year to date slicer is active, it is adding up the individual monthly averages (which work) instead of working out the previous 5 year average of the period 1st April to present.

Example - with 2024/25 selected in fiscal year slicer and Fiscal year to date slicer applied,

Previous 5 year average for April selection = 102.6, May = 83.6 [these are correct]. When all months are selected, previous 5 year average = 186.2 (this is the sum of April and May which is not right). The correct result should be 129.6.

=

if(isblank(

VAR _PY5 = CALCULATE( SUM( 'Deliberate Fires'[Sum] ) , PARALLELPERIOD( 'Calendar'[Date] , -12 , MONTH))

VAR _PY4 = CALCULATE( SUM( 'Deliberate Fires'[Sum] ) , PARALLELPERIOD( 'Calendar'[Date] , -24 , MONTH))

VAR _PY3 = CALCULATE( SUM( 'Deliberate Fires'[Sum] ) , PARALLELPERIOD( 'Calendar'[Date] , -36 , MONTH))

VAR _PY2 = CALCULATE( SUM( 'Deliberate Fires'[Sum] ) , PARALLELPERIOD( 'Calendar'[Date] , -48 , MONTH))

VAR _PY1 = CALCULATE( SUM( 'Deliberate Fires'[Sum] ) , PARALLELPERIOD( 'Calendar'[Date] , -60 , MONTH))

VAR _5yeartotal = _PY5+_PY4+_PY3+_PY2+_PY1

RETURN

_5yeartotal/5

),0,

VAR _PY5 = CALCULATE( SUM( 'Deliberate Fires'[Sum] ) , PARALLELPERIOD( 'Calendar'[Date] , -12 , MONTH))

VAR _PY4 = CALCULATE( SUM( 'Deliberate Fires'[Sum] ) , PARALLELPERIOD( 'Calendar'[Date] , -24 , MONTH))

VAR _PY3 = CALCULATE( SUM( 'Deliberate Fires'[Sum] ) , PARALLELPERIOD( 'Calendar'[Date] , -36 , MONTH))

VAR _PY2 = CALCULATE( SUM( 'Deliberate Fires'[Sum] ) , PARALLELPERIOD( 'Calendar'[Date] , -48 , MONTH))

VAR _PY1 = CALCULATE( SUM( 'Deliberate Fires'[Sum] ) , PARALLELPERIOD( 'Calendar'[Date] , -60 , MONTH))

VAR _5yeartotal = _PY5+_PY4+_PY3+_PY2+_PY1

RETURN

_5yeartotal/5)

Slicer Difference vs Previous 5 year average =[Slicer current year selection]-[Previous5yearaverage]

Slicer % difference vs previous 5 year average =if(iserror([Slicer Difference vs Previous 5 year average] / [Previous5yearaverage]),0,[Slicer Difference vs Previous 5 year average] / [Previous5yearaverage])

[Anonymous]

Hi @gc27 ,

Based on the information you have provided, you can follow the steps below:

1. Your fiscal year begins on 1 April and you may need to adjust your calculations to accurately reflect this. Please consider using the DATESINPERIOD function instead. See the following link for more details:
   DATESINPERIOD function (DAX) - DAX | Microsoft Learn
2. Check that the slicer is affecting the data correctly to ensure that it is linking to the calendar table correctly and that the metrics are responding correctly.

How to Get Your Question Answered Quickly - Microsoft Fabric Community

If it does not help, please provide more details with your desired out put and pbix file without privacy information.

Best Regards,

Ada Wang

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

[gc27]

Thank you for the reply and apologies for the delay in mine.

I found a article online that discussed the differences between sameperiodlastyear, datediff and parallel period.

I cant remember which one i used but it produced the correct answer

[Anonymous]

Glad to hear you have resolved the issue.

Best Regards,

Ada Wang