Forum Discussion

tagban's avatar
tagban
Icon for Helper I rankHelper I
3 years ago
Solved

Compare 2 dates to determine same month

I'm trying to write a DAX measure and having errors when trying to compare two dates. I had found some answers here in the past for other issues I ran into but am relatively new to DAX and M. Basically I want to determine if two column's dates are within the same month. The second column has NULL Values if they don't have a date associated with that yet. 

So originally I was going to use DateDiff using STARTOFMONTH[Date1], vs STARTOFMONTH[Date2]. That way I could see if there was any difference and make it insanely simple.

Problem is Date2 often has NULL values (As its expected to), basically checking to see if someone registered and then un-registered within the same month. Any help is appreciated.

DateDiff(STARTOFMONTH(Registration[enroll_start_date]), STARTOFMONTH(Registration[disenroll_date]),MONTH)

If this would be better in PowerQuery I'm not opposed to it. Just going with the stuff I understand better first. 
Need Help
  • tagban's avatar
    tagban
    3 years ago

    I actually found a solution, creating a new COLUMN:
    Same Month Unregister = SWITCH(True,Month(Query1[register_date]) == Month(Query1[unregister_date]) && Year(Query1[register_date]) == Year(Query1[unregister_date]), "Yes", 1=1, "No")

3 Replies

  • rsbin's avatar
    rsbin
    Icon for Community Champion rankCommunity Champion
    3 years ago

    tagban ,

    Personally, would use the MONTH function.  Create a calculated column:

    SameMonth = SWITCH(
                TRUE()
                ISBLANK([Column2]), 0,  //or whatever you need to enter here
                MONTH([Column1]) = Month([Column2]), "SameMonth" ) //or whatever

    Trust something like this will work for you.

    Regards,

     

    • tagban's avatar
      tagban
      Icon for Helper I rankHelper I
      3 years ago

      I wound up finding a solution using a new Column. Probably not the most elegant and yours actually works as well, but I also have to compare year since this is 9 years of data to look back on. Thank you so much for offering assistance!

  • tagban's avatar
    tagban
    Icon for Helper I rankHelper I
    3 years ago

    I actually found a solution, creating a new COLUMN:
    Same Month Unregister = SWITCH(True,Month(Query1[register_date]) == Month(Query1[unregister_date]) && Year(Query1[register_date]) == Year(Query1[unregister_date]), "Yes", 1=1, "No")