Forum Discussion
Measure to show difference from previous base field
I have a pivot table in Excel that shows the difference of values from the previous base item. How can I do the same in Power BI through a DAX measure? Below is image of the settings I would use in Excel. Thanks.
ahipon , You can check quick measures
with help from date table, you can use time intelligence
example measures
MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD('Date'[Date]))
last MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(dateadd('Date'[Date],-1,MONTH)))
last month Sales = CALCULATE(SUM(Sales[Sales Amount]),previousmonth('Date'[Date]))next month Sales = CALCULATE(SUM(Sales[Sales Amount]),nextmonth('Date'[Date]))
this month = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(ENDOFMONTH('Date'[Date])))
use measures from above
diff = [MTD Sales]-[last MTD Sales]
diff % = divide([MTD Sales]-[last MTD Sales],[last MTD Sales])Power BI — Month on Month with or Without Time Intelligence
https://medium.com/@amitchandak.1978/power-bi-mtd-questions-time-intelligence-3-5-64b0b4a4090e
https://www.youtube.com/watch?v=6LUBbvcxtKA
5 Replies
- amitchandak
Super User
ahipon , You can check quick measures
with help from date table, you can use time intelligence
example measures
MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD('Date'[Date]))
last MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(dateadd('Date'[Date],-1,MONTH)))
last month Sales = CALCULATE(SUM(Sales[Sales Amount]),previousmonth('Date'[Date]))next month Sales = CALCULATE(SUM(Sales[Sales Amount]),nextmonth('Date'[Date]))
this month = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(ENDOFMONTH('Date'[Date])))
use measures from above
diff = [MTD Sales]-[last MTD Sales]
diff % = divide([MTD Sales]-[last MTD Sales],[last MTD Sales])Power BI — Month on Month with or Without Time Intelligence
https://medium.com/@amitchandak.1978/power-bi-mtd-questions-time-intelligence-3-5-64b0b4a4090e
https://www.youtube.com/watch?v=6LUBbvcxtKAI tried checking quick measures but couldn't find a "difference from" option to select a previous base item. Is there a way to calculate previous value without using a date table?
Thanks for your help.
I think I was able to use your measures as a guide. This is what I came up with for my measures without using a separate date table.
Previous USD Value = CALCULATE(SUM(Table1[USD Value]),PREVIOUSMONTH('Table1'[Calendar Month Year]))USD Diff = CALCULATE(SUM(Table1[USD Value]) - [Previous USD Value])
- amitchandak
ahipon , You can check quick measures
with help from date table, you can use time intelligence
example measures
MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD('Date'[Date]))
last MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(dateadd('Date'[Date],-1,MONTH)))
last month Sales = CALCULATE(SUM(Sales[Sales Amount]),previousmonth('Date'[Date]))next month Sales = CALCULATE(SUM(Sales[Sales Amount]),nextmonth('Date'[Date]))
this month = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(ENDOFMONTH('Date'[Date])))
use measures from above
diff = [MTD Sales]-[last MTD Sales]
diff % = divide([MTD Sales]-[last MTD Sales],[last MTD Sales])Power BI — Month on Month with or Without Time Intelligence
https://medium.com/@amitchandak.1978/power-bi-mtd-questions-time-intelligence-3-5-64b0b4a4090e
https://www.youtube.com/watch?v=6LUBbvcxtKA Excel has a functionality named "% Difference from" to be used on pivot table to calculate the variance between a value against previous valu row.
I would like to do the same function in Power BI to calculate the % Diff Ordered Units and % Diff Avrg Retail with the option to select by Year, by Week or by Date by item level.
