Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Enhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.

Reply
Anonymous
Not applicable

How to check month is available or not using Dax

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,
PatilPrasad027_0-1720607714218.png

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?

1 ACCEPTED SOLUTION
Anonymous
Not applicable

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

View solution in original post

2 REPLIES 2
Greg_Deckler
Community Champion
Community Champion

@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.



Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
DAX For Humans

DAX is easy, CALCULATE makes DAX hard...
Anonymous
Not applicable

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

Helpful resources

Announcements
July 2025 community update carousel

Fabric Community Update - July 2025

Find out what's new and trending in the Fabric community.

July PBI25 Carousel

Power BI Monthly Update - July 2025

Check out the July 2025 Power BI update to learn about new features.

Join our Fabric User Panel

Join our Fabric User Panel

This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.