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

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