Change KPI value to previuos month Value if Current month value is not avaiable

[Anonymous]

Hello All,

I have created the custom kpi using below measure.

PlanArrows = IF(ISBLANK([PlanSum]),"No Data Available ",[PlanSum])&" "&IF([PlanSum]=[PlanPrevMon],"",IF([PlanSum] > [PlanPrevMon],UNICHAR(8679),UNICHAR(8681))&IF([PlanSum]<=0,"",""))

And it works fine for me for perticular month which i choose from chicklet slicer of months.

But  here i dont want user to choose the month values from slicer.

I would like to show the card values as 

if current month value is not availble then it should compare with the previous month value automatically.

like

In above image Apr-2017 value is not available then it should compare with the Mar-2017 value.

If Mar-2017 value also not available then the previous mont value.Keep on goes

Any suggestions.

Mohan V

[Anonymous]

Any suggestions....

[v-sihou-msft]

@Anonymous

In this scenario, you can change the true part result into BLANK() in your measure.

PlanArrows = IF(ISBLANK([PlanSum]),BLANK(),[PlanSum])&" "&IF([PlanSum]=[PlanPrevMon],"",IF([PlanSum] > [PlanPrevMon],UNICHAR(8679),UNICHAR(8681))&IF([PlanSum]<=0,"",""))

Then you can use LASTNONBLANK() function like:

=
IF (
    [PlanArrows] = BLANK (),
    CALCULATE ( [PlanArrows], LASTNONBLANK ( Table[Date], [PlanArrows] ) ),
    [PlanArrows]
)

Regards,

[Anonymous · Accepted solution]

@v-sihou-msft Thanks for the reply.

I tried what you have suggested but it giving me the worng results as below image.

Measure = 
IF (
    [PlanArrows] = BLANK (),
    CALCULATE ( [PlanArrows], LASTNONBLANK ( 'Month Year'[Date], [PlanArrows] ) ),
    [PlanArrows]
)

It is giving me the sum of all month values, where i need to get the only Dec value.

I have written a measure and when i see the output of the measure i think i got the solution

LastNonBlank Plan = 
var LastNonBlankDate=CALCULATE(MAX('Month Year'[Date]),FILTER(ALL('Month Year'),'Month Year'[Date]<=MAX('Month Year'[Date]) && [PlanSum]<>0))
return
CALCULATE([PlanSum],
	FILTER(ALL('Month Year'),'Month Year'[Date]=LastNonBlankDate)
)

Output:- 

Well i think, as i need, i got the output here.

Thanks for spending your valuable time.