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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
PatilPrasad027
Resolver I
Resolver I

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

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
Super User
Super User

@PatilPrasad027 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!:
The Definitive Guide to Power Query (M)

DAX is easy, CALCULATE makes DAX hard...

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
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

July 2024 Power BI Update

Power BI Monthly Update - July 2024

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

July Newsletter

Fabric Community Update - July 2024

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

Top Solution Authors
Top Kudoed Authors