Calculate a value on last non blank date

[Anonymous]

I have a list of dates and some values associated to them as shown below. I want to calculate the value which was on previous date.

I have ranked the dates and I am trying to find the value of the column on the previous rank but I get blanks.

Rank By Date = RANK.EQ('P5: Previous month value'[Date],'P5: Previous month value'[Date], ASC)

required value calc = CALCULATE(MAX('P5: Previous month value'[Users]), 'P5: Previous month value'[Rank By Date] = 'P5: Previous month value'[Rank By Date] - 1)

date	value	required output
08/21/2018	1	0
08/23/2018	2	1
09/01/2018	5	2
09/02/2018	1	5
09/03/2018	2	1
09/04/2018	3	2
09/05/2018	3	3
09/06/2018	4	3
09/07/2018	5	4
09/08/2018	6	5

[v-yuezhe-msft · Accepted solution]

@Anonymous,

Create the column using DAX below.

required value calc = var pre =CALCULATE(MAX('P5: Previous month value'[value]),FILTER('P5: Previous month value','P5: Previous month value'[Rank By Date]=EARLIER('P5: Previous month value'[Rank By Date])-1)) return IF(ISBLANK(pre),0,pre)

Regards,
Lydia