Forum Discussion
% 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 % Difference between the average of a user defined time frame from 2019 compared to the same time frame from 2020.
For example, I'd like to take % Difference of the average of the entries in January, February, March of 2019 of the Lane Clearance Duration column below and the average of the entries of the January, February, March of 2020 of the same column. I'd like to be able to choose the time frame on a more user defined basis, say comparing the same month, or quarter, or year.
Let me know if you need any additional information.
Thanks in advance.
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.
1 Reply
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.