0 votes
1 view
in BI by (17.6k points)

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

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

1 Answer

0 votes
by (47.2k points)

enter image description here

  • 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])))

Related questions

0 votes
1 answer
0 votes
1 answer
0 votes
1 answer
0 votes
1 answer
Welcome to Intellipaat Community. Get your technical queries answered by top developers !


Categories

...