Forum Discussion

carlochecchia's avatar
carlochecchia
Advocate I
5 years ago
Solved

Calculate Difference Based on Last Entry (Date and Time Column)

Hi there,   I have the following table and would like to get some help in regards to how to calculate the column called 'Change' I would like to create a Dax Measure that calculates the difference...
Need Help
  • carlochecchia's avatar
    carlochecchia
    5 years ago

    In case it was tough to understand the data, here it goes again

    DateTime NetProducts Change
    22/07/2021 20:30                 1.780.022 --
    22/07/2021 20:35                 1.780.022 0
    22/07/2021 21:00                 1.780.037 -15
    22/07/2021 21:04                 1.780.037 0
    22/07/2021 21:05                 1.780.037 0
    22/07/2021 21:15                 1.780.037 0
    22/07/2021 21:19                 1.780.037 0
    22/07/2021 21:42                 1.780.049 -12
    22/07/2021 21:42                 1.780.049 0
    22/07/2021 21:56                 1.780.049 0
    22/07/2021 22:02                 1.780.049 0
    22/07/2021 22:12                 1.780.049 0
    22/07/2021 22:46                 1.780.064 -15
  • sevenhills's avatar
    sevenhills
    5 years ago
    Change2 = 
var _cValDT = SELECTEDVALUE('Table'[DateTime])
var _cValStore = SELECTEDVALUE('Table'[Store])
var _cVal = SELECTEDVALUE('Table'[NetProducts])
var _pValDT = Maxx(Filter( ALLSELECTED('Table'),  'Table'[DateTime] < _cValDT && 'Table'[Store] = _cValStore), 'Table'[DateTime])
var _pValDT_Val = Maxx(Filter( ALLSELECTED('Table'),  'Table'[DateTime] = _pValDT && 'Table'[Store] = _cValStore), 'Table'[NetProducts])
Return IF ( ISBLANK(_pValDT_Val), "--", ( _cVal - _pValDT_Val))

     

    Mark this one as answer to the solution. (I saw you marked your question post as answer)

sevenhills's avatar
sevenhills
Super User
5 years ago

Create the measure as below and format the measure as whole number. 

Change the names to your needs

 

Change = 
var _pValDT = Maxx(Filter( ALLSELECTED(TableChangeCalc),  TableChangeCalc[DateTime] < SELECTEDVALUE(TableChangeCalc[DateTime]) ), TableChangeCalc[DateTime])

var _pValDT_Val = Maxx(Filter( ALLSELECTED(TableChangeCalc),  TableChangeCalc[DateTime] = _pValDT), TableChangeCalc[NetProducts])

Return IF ( ISBLANK(_pValDT_Val), "--", ( _pValDT_Val - SELECTEDVALUE(TableChangeCalc[NetProducts])))

 

carlochecchia's avatar
carlochecchia
Advocate I
5 years ago

Thank you so much. Your DAX formula did the job.
I was wondering if you could help me improve this DAX measure to take in account the value for the last date/hour based on a specific store branch.

 

It´s the same scenario described above, but with an extra column that has information on the store branch.
I would like the DAX calculation to take into account the store branch´s last available data.

I would like to obtain the results from the second table in my image (change column).
As opposed to what i am getting with the results in the change calculation (third table from my image)

 

Thanks in advance for the help!

 



  • sevenhills's avatar
    sevenhills
    Super User
    5 years ago

    Could you attach the pbix file (removing sensitive info) so that I can look into it ? Thanks

     

    Or try this ... 

    Change2 = 
var _cValDT = SELECTEDVALUE('TableChangeCalc (2)'[DateTime])
var _cValStore = SELECTEDVALUE('TableChangeCalc (2)'[Store])
var _cVal = SELECTEDVALUE('TableChangeCalc (2)'[NetProducts])

var _pValDT = Maxx(Filter( ALLSELECTED('TableChangeCalc (2)'),  'TableChangeCalc (2)'[DateTime] < _cValDT && 'TableChangeCalc (2)'[Store] = _cValStore), 'TableChangeCalc (2)'[DateTime])

var _pValDT_Val = Maxx(Filter( ALLSELECTED('TableChangeCalc (2)'),  'TableChangeCalc (2)'[DateTime] = _pValDT && 'TableChangeCalc (2)'[Store] = _cValStore), 'TableChangeCalc (2)'[NetProducts])

Return IF ( ISBLANK(_pValDT_Val), "--", ( _pValDT_Val - _cVal))

     

      • sevenhills's avatar
        sevenhills
        Super User
        5 years ago
        Change2 = 
var _cValDT = SELECTEDVALUE('Table'[DateTime])
var _cValStore = SELECTEDVALUE('Table'[Store])
var _cVal = SELECTEDVALUE('Table'[NetProducts])
var _pValDT = Maxx(Filter( ALLSELECTED('Table'),  'Table'[DateTime] < _cValDT && 'Table'[Store] = _cValStore), 'Table'[DateTime])
var _pValDT_Val = Maxx(Filter( ALLSELECTED('Table'),  'Table'[DateTime] = _pValDT && 'Table'[Store] = _cValStore), 'Table'[NetProducts])
Return IF ( ISBLANK(_pValDT_Val), "--", ( _cVal - _pValDT_Val))

         

        Mark this one as answer to the solution. (I saw you marked your question post as answer)