Forum Discussion

ahipon's avatar
ahipon
Frequent Visitor
4 years ago
Solved

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...
Need Help
  • amitchandak's avatar
    amitchandak
    4 years ago

    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

amitchandak's avatar
amitchandak
Icon for Super User rankSuper User
4 years ago

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's avatar
ahipon
Frequent Visitor
4 years ago

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