Forum Discussion

EuanKonnektis's avatar
EuanKonnektis
Frequent Visitor
6 years ago

Calculate difference between Matrix Columns

Hi Everyone,   I feel like I've seen a multitude of solutions for questions similar to my own here, but can't find the one that tackles the way my report is built! If anyone is able to help me, it ...
Need Help
amitchandak's avatar
amitchandak
Super User
6 years ago

EuanKonnektis 

You can create a last day measure and take diff and use that

 

Last Day Non Continous = CALCULATE(sum('order'[Qty]),filter(all(Table),Table[Date] =MAXX(FILTER(all(Table),Table[Date]<max(Table[Date])),Table[Date])))

or
Day behind Sales = CALCULATE(SUM(Sales[Sales Amount]),dateadd(Table[Date],-1,Day))

 

This Day = sum('order'[Qty])

 

diff = [This Day ] -[Last Day Non Continous] // Or use [Day behind Sales]

 

To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s. Refer :
https://radacad.com/creating-calendar-table-in-power-bi-using-dax-functions
https://www.archerpoint.com/blog/Posts/creating-date-table-power-bi
https://www.sqlbi.com/articles/creating-a-simple-date-table-in-dax/

  • EuanKonnektis's avatar
    EuanKonnektis
    Frequent Visitor
    6 years ago

    Hi amitchandak, thanks ever so much for your advice here! I've used the day behind sales to help with tracking the increase over -7 days which has been really useful!

     

    I just haven't cracked using the Non-Continous Formula you've provided to help with when the export dates don't have a continuous 1,2,3 days export. I wondered if you could take a look at what I've tried? I'm not sure I've written your measure correctly. So this is the measure I'm trying to build to use to compare with the latest Export figure.

     

    Here's a key to help show what I'm working with.

     

    Table = 'Data Usage'

    Value = 'Data Usage[Usage (MB)]

    Date value = 'Data Usage'[Export Date]

     

    Previous Export = CALCULATE(SUM('Data Usage'[Usage (MB)]),FILTER('Data Usage','Data Usage'[Export Date] =MAXX(FILTER('Data Usage','Data Usage'[Export Date]<MAX('Data Usage'[Export Date])),'Data Usage'[Export Date])))
     
    Thanks in advance,
    Euan.