topic Re: How to check month is available or not using Dax in DAX Commands and Tips

How to check month is available or not using Dax

Anonymous — Wed, 10 Jul 2024 10:37:39 GMT

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?

Re: How to check month is available or not using Dax

Greg_Deckler — Wed, 10 Jul 2024 12:16:11 GMT

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.

Re: How to check month is available or not using Dax

Anonymous — Wed, 10 Jul 2024 12:50:31 GMT

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")