Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

% Difference Between Average Measures

Hello, I've been reviewing posts on % Differences and haven't been able to find one that is similar to my situation.   I have one Queury that contains 2019 and 2020 data. I'd like to find the % Di...
  • AllisonKennedy's avatar
    5 years ago

    Anonymous  Do these two queries both have the same columns? All the percent difference calcuations work on one column, so this will be easier if you append both queries into one table. 

     

    https://docs.microsoft.com/en-us/power-query/append-queries

     

    Then you'll need a data table to be able to use time intelligence: https://excelwithallison.blogspot.com/2020/04/dimdate-what-why-and-how.html

     

    Finally, create your base measure, for example:

     

    Average Duration = AVERAGE(AppendedTable[Event Duration])

     

    Then use DATEADD to create the Previous year measure:

    PY Average Duration = CALCULATE( [Average Duration], DATEADD(Date[Date], -12, Month) )

     

    Finally % Diff is: 

    YoY Percent Change Average Duration = DIVIDE( [Average Duration] - [PY Average Duration] , [PY Average Duration] )

     

    Then put all measures in a visual with Year, Quarter, Month from your Date table and let users decide which level they want to see.