Return N/A value

[Anonymous]

Hi,

Just wondering if anyone cn help with my query, I need the below DAX to return N/A when the current year is not selected on the filter I have on the page. Wehn I select 2020 for instance I get Blank in my card visual 

Prev Months Tickets = VAR CurrentMonth = MONTH(TODAY())

VAR currntYear =  YEAR(TODAY())

VAR LastDay = EOMONTH(TODAY()),0)

VAR Firstday = DATE(CurrentYear, CurrentMonth,1)

VAR ContextTable = 

CALCULATETABLE(

      VALUES(Dates[Date]),

      Filter(Dates,

                Dates[Date] >= Firstday &&

                Dates[Date] <= LastDay))

RETURN

CALCUALTE([Total Tickets],

DATEADD(ContextTable, -1, MONTH))

Many thanks in advance

[d_gosbell · Accepted solution]

This is a bit annoying, I wish the card had a setting for alternate text to return when the result is blank. But you can do this in a measure by explicitly checking for the blank value

eg

Prev Months Tickets = VAR CurrentMonth = MONTH(TODAY())

VAR currntYear =  YEAR(TODAY())

VAR LastDay = EOMONTH(TODAY()),0)

VAR Firstday = DATE(CurrentYear, CurrentMonth,1)

VAR ContextTable = 

CALCULATETABLE(

      VALUES(Dates[Date]),

      Filter(Dates,

                Dates[Date] >= Firstday &&

                Dates[Date] <= LastDay))

var result = 

CALCUALTE([Total Tickets],

DATEADD(ContextTable, -1, MONTH))

RETURN IF( ISBLANK( result ), "N/A", result)

You could also look at using the COALESCE function for the last line

RETURN COALESCE( result , "N/A" )

[Anonymous]

Hi @d_gosbell 

Thank you so much both solutions work perfectly, plus I learnt the COALESCE function which is even better so thanks again.