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

[BunnyMadhu]

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

[/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]

[/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]

 

[jorismoerings]

Hi,

try this:

Excel 2016 (Windows) 64 bit
	A	B	C	D	E	F	G	H	I	J
1	Year	Quarter	Month	Day	PlanSum	PlanPrevMon
2					1
3						1
4
5					4
6						4		Mon	Prev Mon	Plan Arrow
7								4	1	?
Sheet1

Cell Formulas
Range	Formula
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.

 

[BunnyMadhu]

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]