Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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.
Solved! Go to Solution.
@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.
Copying DAX from this post? Click here for a hack to quickly replace it with your own table names
Has this post solved your problem? Please Accept as Solution so that others can find it quickly and to let the community know your problem has been solved.
If you found this post helpful, please give Kudos C
I work as a Microsoft trainer and consultant, specialising in Power BI and Power Query.
www.excelwithallison.com
@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.
Copying DAX from this post? Click here for a hack to quickly replace it with your own table names
Has this post solved your problem? Please Accept as Solution so that others can find it quickly and to let the community know your problem has been solved.
If you found this post helpful, please give Kudos C
I work as a Microsoft trainer and consultant, specialising in Power BI and Power Query.
www.excelwithallison.com