Measure to show difference from previous base field

[ahipon]

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.

[Hammer-PBI]

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. 

[amitchandak · Accepted solution]

@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

[ahipon]

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])

[ahipon]

I 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?

[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