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 col...
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

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