Search code examples
powerbidaxpowerquerym

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

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 cards visuals.

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

Sample Data:- enter image description here

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 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

enter image description here

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

Edit

I tried what @user5226582 suggested but still getting wrong values as below image.

enter image description here

As you can see i restricted to crosscheck whether the value is getting in a right way of not.

But not.

This is the measure i have used as @user5226582 suggested.

c = 
var temp = 'Revenue Report'[Date]
return CALCULATE(LASTNONBLANK('Revenue Report'[Planned Rev],
  'Revenue Report'[Planned Rev]),
  ALL('Revenue Report'),
  'Revenue Report'[Date]<=temp)`

Can you please correct me if i am doing anything wrong

Solution

  • Does this help...?

    enter image description here

    To get the PlanPrevMon column to show like this, I first created a new index column:

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

    Then I used the index to help 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])))

    I hope this helps.