Change Custom KPI value to previous month Value if Current month value is not available

BunnyMadhu

New Member
Joined
May 13, 2017
Messages
3
[FONT=&quot]Hello All,[/FONT]
[FONT=&quot] [/FONT]
[FONT=&quot]I have created the custom kpi using below measure.[/FONT]
[FONT=&quot] [/FONT]
PlanArrows = IF(ISBLANK([PlanSum]),"No Data Available ",[PlanSum])&" "&IF([PlanSum]=[PlanPrevMon],"",IF([PlanSum] > [PlanPrevMon],UNICHAR(8679),UNICHAR(8681))&IF([PlanSum]<=0,"",""))[FONT=&quot]And it works fine for me for perticular month which i choose from chicklet slicer of months.[/FONT]
[FONT=&quot]
large
[/FONT]

[FONT=&quot] [/FONT]
[FONT=&quot]But here i dont want user to choose the month values from slicer.[/FONT]
[FONT=&quot]I would like to show the card values as [/FONT]
[FONT=&quot]if current month value is not availble then it should compare with the previous month value automatically.[/FONT]
[FONT=&quot]like[/FONT]
[FONT=&quot]
large
[/FONT]

[FONT=&quot]In above image Apr-2017 value is not available then it should compare with the Mar-2017 value.[/FONT]
[FONT=&quot]If Mar-2017 value also not available then the previous mont value.Keep on goes[/FONT]
[FONT=&quot] [/FONT]
[FONT=&quot]Any suggestions.[/FONT]
[FONT=&quot]Mohan V[/FONT]
 

Excel Facts

Is there a shortcut key for strikethrough?
Ctrl+S is used for Save. Ctrl+5 is used for Strikethrough. Why Ctrl+5? When you use hashmarks to count |||| is 4, strike through to mean 5.
Hi,

try this:


Excel 2016 (Windows) 64 bit
ABCDEFGHIJ
1YearQuarterMonthDayPlanSumPlanPrevMon
21
31
4
54
64MonPrev MonPlan Arrow
741?
Sheet1
Cell Formulas
RangeFormula
J7=IF(H7=I7,"",IF(H7/I7>I7,UNICHAR(8679),UNICHAR(8681)))
H7{=INDEX(Table1[PlanSum],LARGE((ABS(Table1[PlanSum])>0)*ROW(Table1[PlanSum]),1)-(CELL("Row",E2)-1))}
I7{=INDEX(Table1[PlanPrevMon],LARGE((ABS(Table1[PlanPrevMon])>0)*ROW(Table1[PlanPrevMon]),2)-(CELL("Row",E2)-1))}
Press CTRL+SHIFT+ENTER to enter array formulas.
 
Upvote 0
Thanks for the reply jorismoerings

But I am working with the dax queries.
How can i covert these excel formulas into Dax.
I guess
[FONT=&quot]DAX isn't really designed for referencing individual cells like excel formulas.

[/FONT]
 
Upvote 0

Forum statistics

Threads
1,223,967
Messages
6,175,664
Members
452,666
Latest member
AllexDee

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top