cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
sdas028 Regular Visitor
Regular Visitor

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

Accepted Solutions
Community Support Team
Community Support Team

Re: IF Statement DAX Month

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 other members find it more quickly.
7 REPLIES 7
Super User
Super User

Re: IF Statement DAX Month

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
 
edhans New Contributor
New Contributor

Re: IF Statement DAX Month

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
)

 

 

edhans New Contributor
New Contributor

Re: IF Statement DAX Month

Note that @rajendran 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. :-) 

Community Support Team
Community Support Team

Re: IF Statement DAX Month

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 other members find it more quickly.
sdas028 Regular Visitor
Regular Visitor

Re: IF Statement DAX Month

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

Community Support Team
Community Support Team

Re: IF Statement DAX Month

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 other members find it more quickly.
sdas028 Regular Visitor
Regular Visitor

Re: IF Statement DAX Month

Hi Frank,

 

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

 

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

 

Thanks,

Sophie