Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Solved! Go to 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.
Any suggestions....
@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,
@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.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
111 | |
94 | |
80 | |
68 | |
59 |
User | Count |
---|---|
150 | |
119 | |
104 | |
87 | |
67 |