Forum Discussion

carlochecchia's avatar
carlochecchia
Icon for Advocate I rankAdvocate 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 between the value in column NetProducts and the previous value available.

Thanks in advance!

 

DateTimeNetProductsChange
22/07/2021 20:301.780.022--
22/07/2021 20:351.780.0220
22/07/2021 21:001.780.037-15
22/07/2021 21:041.780.0370
22/07/2021 21:051.780.0370
22/07/2021 21:151.780.0370
22/07/2021 21:191.780.0370
22/07/2021 21:421.780.049-12
22/07/2021 21:421.780.0490
22/07/2021 21:5617800490
22/07/2021 22:0217800490
22/07/2021 22:1217800490
22/07/2021 22:461780064-15
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)

7 Replies

  • carlochecchia's avatar
    carlochecchia
    Icon for Advocate I rankAdvocate I
    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
      Icon for Super User rankSuper 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
        Icon for Advocate I rankAdvocate 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!