Column with the previous day value

[Anonymous]

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

Date	ID	Value	Result
01/01/2020	ABC	100	-
02/01/2020	ABC	200	100
5/01/2020	ABC	150	200
06/01/2020	ABC	110	150
08/01/2020	ABC	300	110

Thanks for the help

Agus

[amitchandak]

@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]

I dont understand two things of your DAX:

- Earlier[ID] ?

- [Result]?

Thanks

[v-jayw-msft · Accepted solution]

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