Forum Discussion

sdas028's avatar
sdas028
Helper I
7 years ago
Solved

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

 

Need Help
  • v-frfei-msft's avatar
    v-frfei-msft
    7 years ago

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

    For more details, please check the pbix as attached.

     

    Regards,
    Frank

7 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    7 years ago

    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's avatar
    edhans
    Community Champion
    7 years ago

    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's avatar
      edhans
      Community Champion
      7 years ago

      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. :-) 

      • sdas028's avatar
        sdas028
        Helper I
        7 years ago

        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

  • v-frfei-msft's avatar
    v-frfei-msft
    Community Support
    7 years ago

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

    For more details, please check the pbix as attached.

     

    Regards,
    Frank