powerbidaxpowerquerypowerbi-desktoppowerbi-custom-visuals

How to calculate Annual Variance on Monthly basis?

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

Solution

  • 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