cancel
Showing results for
Did you mean:
Established Member

## Calculate a value on last non blank date

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

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Moderator

## Re: Calculate a value on last non blank date

@mnayar,

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

Community Support Team _ Lydia Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Highlighted
Moderator

## Re: Calculate a value on last non blank date

@mnayar,

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

Community Support Team _ Lydia Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.