Forum Discussion
KPI Card Inifite number problem
Hello,
I'm in the middle of creating a simple report, which uses KPI Card to display sales for current month, and sales for previous month.
Although it works fine when there is a continuity for date, however problem occurs when there is a gap in months.
To avoid such situation when there is a sentence (Blank) (+Infinity), and display as target value "N/A", I've modifided measure which stands for sales for previous month.
Sales for previous month =
var date1 = EDATE(MAX(DimCalendar[Date]),-1) // to jest november
var vvalue = CALCULATE(SUM(vFactSales[Sales]), YEAR(date1) = YEAR(vFactSales[Date]) , MONTH(date1) = MONTH(vFactSales[Date]))
var vvalue2 = IF(ISBLANK(vvalue),"N/A",vvalue)
return vvalue2
So even though, it looks that in the table value for that measure is blank, and I write in my DAX code, that if it is blank then it should be "NA", it gives weird output. Could anyone help me or guide here, what I'm doing wrong?
Best regards,
Hubert
Hi Berrcikk ,
My test data is as follows：You can try the DAX below：
Sales for current month = VAR Current_Month = SELECTEDVALUE('Table'[date].[MonthNo]) VAR SalesCurrentMonth = CALCULATE(SUM('Table'[sales]), 'Table'[date].[MonthNo] = Current_Month) RETURN IF(ISBLANK(SalesCurrentMonth), "N/A", SalesCurrentMonth)Sales for previous month = VAR Current_Month = SELECTEDVALUE('Table'[date].[MonthNo]) VAR Previous_Month = Current_Month - 1 VAR SalesPreviousMonth = CALCULATE(SUM('Table'[sales]), 'Table'[date].[MonthNo] = Previous_Month,ALL('Table')) RETURN IF(ISBLANK(SalesPreviousMonth), "N/A", SalesPreviousMonth)The final result looks like this:
Best Regards,
Dino Tao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
1 Reply
Hi Berrcikk ,
My test data is as follows：You can try the DAX below：
Sales for current month = VAR Current_Month = SELECTEDVALUE('Table'[date].[MonthNo]) VAR SalesCurrentMonth = CALCULATE(SUM('Table'[sales]), 'Table'[date].[MonthNo] = Current_Month) RETURN IF(ISBLANK(SalesCurrentMonth), "N/A", SalesCurrentMonth)Sales for previous month = VAR Current_Month = SELECTEDVALUE('Table'[date].[MonthNo]) VAR Previous_Month = Current_Month - 1 VAR SalesPreviousMonth = CALCULATE(SUM('Table'[sales]), 'Table'[date].[MonthNo] = Previous_Month,ALL('Table')) RETURN IF(ISBLANK(SalesPreviousMonth), "N/A", SalesPreviousMonth)The final result looks like this:
Best Regards,
Dino Tao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.