cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
mnayar Established Member
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 valuerequired output
08/21/201810
08/23/201821
09/01/201852
09/02/201815
09/03/201821
09/04/201832
09/05/201833
09/06/201843
09/07/201854
09/08/201865

 

previous date value.PNG

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Moderator v-yuezhe-msft
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)

2.PNG

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.
1 REPLY 1
Highlighted
Moderator v-yuezhe-msft
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)

2.PNG

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.