PowerBi Change Card values to previous month Value if Current month value is not available

Question

I am working on powerbi, using data over 2 months period, writing DAX queries and trying to build reports.

I am trying to show the monthly values in card visuals.

And I am able to do it using the measure below.

Sample Data:- 

PlanPrevMon = CALCULATE([PlanSum],PREVIOUSMONTH('Month Year'[Date]))

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

But here I don't want the user to choose the month values from slicer. I would like to show the card values as if current month value is not available then it should compare with the previous month value automatically as shown in an image below

For example, Apr-2017 value is not available; in this scenario, I would like it to compare with the Mar-2017 value. If Mar-2017 value also not available, then the previous month value and so on

Answer 1

• In order to get the PlanPrevMon column to show like this, we have to create a new index column:

id = COUNTROWS(FILTER(Sheet1, EARLIER(Sheet1[Date],1)>Sheet1[Date]))

• Then use the index to create the PlanPrevMon column in two steps:

Step 1: I made one column named PlanPrevMon1.

PlanPrevMon1 = SUMX(FILTER(Sheet1,Sheet1[id]=EARLIER(Sheet1[id])-1),Sheet1[PlanSum])

Step 2: I made another column named PlanPrevMon.

PlanPrevMon = if(ISBLANK(Sheet1[PlanPrevMon1]), if(Sheet1[id]=1,0,CALCULATE(LASTNONBLANK(Sheet1[PlanPrevMon1],Sheet1[PlanPrevMon1]),ALL(Sheet1),ISBLANK(Sheet1[PlanSum]))), Sheet1[PlanPrevMon1])

• For the card, I used this measure:

Card = CALCULATE(LASTNONBLANK(Sheet1[PlanPrevMon],1),FILTER(Sheet1,Sheet1[id]=max(Sheet1[id])))