Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

Column with the previous day value

HI,

 

I have a column with dates and values. The date column is not including some days, so I need to create a column with the value of the previous date like this one (Also the table has an ID column with different ID's

 

DateIDValueResult
01/01/2020ABC100-
02/01/2020ABC200100
5/01/2020ABC150200
06/01/2020ABC110150
08/01/2020ABC300110

 

Thanks for the help

Agus

Need Help
  • Anonymous's avatar
    Anonymous
    5 years ago

    Hi Anonymous ,

     

    Check the formula below.

    Column = 
var pre_date = CALCULATE(MAX('Table'[Date]),FILTER(ALLEXCEPT('Table','Table'[ID]),'Table'[Date]<EARLIER('Table'[Date])))
return
CALCULATE(SUM('Table'[Value]),FILTER('Table','Table'[ID]=EARLIER('Table'[ID])&&'Table'[Date]=pre_date))

    Result would be shown as below.

     

    For earlier() function please check the below document.

    https://docs.microsoft.com/en-us/dax/earlier-function-dax 

     

    Best Regards,

    Jay

3 Replies

  • amitchandak's avatar
    amitchandak
    Icon for Super User rankSuper User
    5 years ago

    Anonymous , Try a new column

    New column =
    var _min = maxx(filter(Table, [ID] = earlier([ID]) && [Date] <earlier([Date])),[Date])
    return
    maxx(filter(Table, [ID] = earlier([ID]) && [Date] =_min),[Result])

    • Anonymous's avatar
      Anonymous
      Not applicable
      5 years ago

      I dont understand two things of your DAX:

       

      - Earlier[ID] ?

      - [Result]?

       

      Thanks

      • Anonymous's avatar
        Anonymous
        Not applicable
        5 years ago

        Hi Anonymous ,

         

        Check the formula below.

        Column = 
var pre_date = CALCULATE(MAX('Table'[Date]),FILTER(ALLEXCEPT('Table','Table'[ID]),'Table'[Date]<EARLIER('Table'[Date])))
return
CALCULATE(SUM('Table'[Value]),FILTER('Table','Table'[ID]=EARLIER('Table'[ID])&&'Table'[Date]=pre_date))

        Result would be shown as below.

         

        For earlier() function please check the below document.

        https://docs.microsoft.com/en-us/dax/earlier-function-dax 

         

        Best Regards,

        Jay