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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
sdas028
Helper I
Helper I

IF Statement DAX Month

Hi,

 

I have a date field in a large table of data, I want to have an IF statement in an added column where it says Yes if the month (or date) is equal to or less than todays date and No if it isnt. This will then be used for a simple YTD slicer that says Yes or No, where selecting Yes will filter only the dates before todays date - hope that made sense!

 

I tried this formula: if(MONTH([Posting Date]) < TODAY() then "Yes" else "No"

But I keep getting an error Expression.error: The name MONTH wasn't recognised make sure its spelled correctly

 

Does anyone know what I'm doing wrong? I thought MONTH() was a DAX function

 

Sample Data:

Posting Date      YTD

1/1/19                Yes

1/2/19                Yes

1/3/19                No

1/4/19                No

 

1 ACCEPTED SOLUTION
v-frfei-msft
Community Support
Community Support

Hi @sdas028 ,

 

We can create a calculated column to make sure the reuslt is correct if there are different years in your fact table.

 

YTD = var monthyear = YEAR(Table1[Posting date])*100+MONTH(Table1[Posting date])
var _today = YEAR(TODAY())*100+MONTH(TODAY())
return
IF(monthyear<=_today,"Yes","No")

Capture.PNG

For more details, please check the pbix as attached.

 

Regards,
Frank

Community Support Team _ Frank
If this post helps, then please consider Accept it as the solution to help the others find it more quickly.

View solution in original post

7 REPLIES 7
v-frfei-msft
Community Support
Community Support

Hi @sdas028 ,

 

We can create a calculated column to make sure the reuslt is correct if there are different years in your fact table.

 

YTD = var monthyear = YEAR(Table1[Posting date])*100+MONTH(Table1[Posting date])
var _today = YEAR(TODAY())*100+MONTH(TODAY())
return
IF(monthyear<=_today,"Yes","No")

Capture.PNG

For more details, please check the pbix as attached.

 

Regards,
Frank

Community Support Team _ Frank
If this post helps, then please consider Accept it as the solution to help the others find it more quickly.
edhans
Super User
Super User

It is proabably a misleading error. Month() returns an integer. You are comparing an integer to a date. So DAX is going "If 1 < 2/19/2019 then..." and it doesn't know what to do.

 

IN addition to that, your PostingDates is returning a table, not a scalar value for MONTHS() to work with. Try this. I used MAX() to coax the date out based on the filter context of the pivot table. My sample table has different lables, but you can see what I've done. START is my date field.

 

IF ( 
   MONTH ( MAX ( Dates[Start] ) ) < MONTH ( TODAY () ),
    1,
    0
)

 

 



Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

DAX is for Analysis. Power Query is for Data Modeling


Proud to be a Super User!

MCSA: BI Reporting

Note that @Anonymous and mine do two different things. His will tell you any date older than today. Mine will tell you any month older than today. So Feb 1 is not older than today because month(2/1/2019) is not less than month(today()) (assuming today is any day in the month of Feb 2019.

 

Not sure which you need. Your original example left some room for interpretation. 🙂 



Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

DAX is for Analysis. Power Query is for Data Modeling


Proud to be a Super User!

MCSA: BI Reporting

Hi Edhans,

 

Thanks so much for your answer this seems to be what I need, is it possible to have it less than or equal to todays Month?

So that February is still Yes YTD?

 

Also apologies for the misinterpretation!

 

Thanks,

Sophie

Hi @sdas028 ,

 

Did you have a chance to check my way?

 

Regards,

Frank

Community Support Team _ Frank
If this post helps, then please consider Accept it as the solution to help the others find it more quickly.

Hi Frank,

 

Yes absolutely! This is actually what I ended up using for the solution so thank you very much! 🙂

 

Apologies I always forget to go back and close off the request, will do that now

 

Thanks,

Sophie

Anonymous
Not applicable

Hi @sdas028 

 

if you want to flag the past dates, you can use this formula in your column:

 

Past Date = IF('Table'[Date]< TODAY(),"YES","NO")
 
If you are looking for only current year, ( This can be used for your YTD needs)
 
Current Year Past Date = IF(YEAR('Table'[Date])=YEAR( TODAY()) && 'Table'[Date]<=TODAY() ,"YES","NO")
 
Thanks
Raj
 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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