How to check month is available or not using Dax

[Anonymous]

Hi Community..!!

Below is my measure,

@@6 month range =

var selected_month = SELECTEDVALUE('Automated Spend'[Month-Year])

var future7thMonths = EOMONTH(selected_month,7)

var futureSixMonths = EOMONTH(future7thMonths,5)

RETURN FORMAT(future7thMonths,"mmm'yy") & "- " & FORMAT(futureSixMonths, "mmm'yy")

the above measure output is as follows,

If i select Jan-24 from the slicer then it will display Aug'24 to Jan'25 in the card visual as below image,

The problem here is , Jan'25 month is not present in my dataset then how it is displaying?

and i want the output as "-" if the month is not available in the dataset.

How do we do this by using DAX?

[Greg_Deckler]

@Anonymous EOMONTH is a non-time intelligence function and thus does not require a date table to be able to calculate dates. It's internally wired into the function. What you can do is add a check on your date table or other table to see if the date is present and if not, just return "-". Like:

VAR isPresent = COUNTROWS(FILTER('Automated Spend', [Month-Year] = futureSixMonths))

If that is blank, then the date is not present.

[Anonymous · Accepted solution]

Hi @Greg_Deckler 

Thanks for the suggestion.

But I got the output and below is the solution,

@@6 month range =

var selected_month = SELECTEDVALUE('Automated Spend'[Month-Year])

var future7thMonth = EOMONTH(selected_month,7)

var future12thMonth = EOMONTH(future7thMonth,5)

VAR future12MonthFormatted = FORMAT(future12thMonth, "mmm'yy")

VAR isFuture12thMonthPresent =

    CALCULATE(

        COUNTROWS('Automated Spend'),

        FILTER(

            'Automated Spend',

            FORMAT('Automated Spend'[Month-Year], "mmm'yy") = future12MonthFormatted

        )

    ) > 0

var result = IF(isFuture12thMonthPresent,future12MonthFormatted,BLANK())

RETURN FORMAT(future7thMonth,"mmm'yy") & "- " & FORMAT(result, "mmm'yy")