Forum Discussion
% Difference Between Average Measures
- 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.
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.