How to calculate Annual Variance on Monthly basis?

Question

Am new to Power BI and appreciate help on DAX for this requirement:

I have a FY slicer (July-June) in my Power BI reports and wants to show monthly trending across different FY years. Target data is structured like this in a table:

Dates | Target

30-06-2018 | 34000

30-07-2018 | 34000

30-08-2018 | 34000

********** | *****

30-06-2019 | 30000

30-07-2019 | 30000

********** | ******

I need to calculate annual variance with below formula to get monthly baseline for next year(2019), then calculate cumulative reduction variance across July-June:

('Target of 30-06-2018') - ('Target of 30-06-2019')/ 12

Dates are linked to another DATE table, which has Financial year and Financial month columns.

Thanks so much in advance for the help!!
Anita

Murray Foxcroft Murray Foxcroft · Accepted Answer · 2020-02-01T12:08:36

You need to use the DAX PARALLELPERIOD

PARALLELPERIOD(<dates>,<number_of_intervals>,<interval>)

The documentation gives a perfect example:

 = CALCULATE(SUM(InternetSales_USD[SalesAmount_USD]), PARALLELPERIOD(DateTime[DateKey],-1,year))

So your calculation will something like:

   = (
      CALCULATE(SUM(TargetTable[Target])) - 
      CALCULATE(SUM(TargetTable[Target]), PARALLELPERIOD(Dates[Date],-1,year))
     ) / 12

If you want monthly variance, then you need to ensure that Dates[Date] is filtered at a grain to return a table of dates for the entire target month.

You can find a great blog on it here

How to calculate Annual Variance on Monthly basis?

2 Answers