Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago

Nested if statement with date comparison

Hello, everyone! I have a task, so I need to migrate some calculations from Tableau to Power BI. One calculation in Tableau that I need to migrate looks like this:   if [Due Date]>=DATETRUNC('month...
Greg_Deckler's avatar
Greg_Deckler
Community Champion
4 years ago

Anonymous You could use nested IF statements like:

IF([Due Date]>= MONTH(TODAY()) || [% Complete] < 1, IF(..., 3), IF(..., 6))

 

However, I would use a SWITCH(TRUE(),...) statement.

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    Thank you so much, Greg! I also think that SWITCH(TRUE()), can be the better solution but I cannot catch the idea how it can looks like with this condition at the top:
    IF([Due Date]>= MONTH(TODAY()) || [% Complete] < 1...)

     

    Also, if you know how to implement this condition 

    `dateadd('month',3,DATETRUNC('month',Today()))`  I would appreciate any ideas:)

    My variant by now looks like this but I'm not sure it is correct (I'm not sure how to combine DATEADD and MONTH(Today()), so I tried to use EOMONTH:

    Next 3 and 6 month Milestone Flag =
    VAR Next3Month = 3
    VAR Next6Month = 6
    RETURN
    SWITCH
    (
    TRUE(),
    [Due Date] >= EOMONTH (TODAY (), 0)
    && [Due Date] <= EOMONTH (TODAY (), Next3Month)
    && The Next 3 and 6 month Milestone Flag = 1, 3,
    [Due Date] >= EOMONTH (TODAY (), 0)
    && [Due Date] <= EOMONTH (TODAY (), Next6Month)
    && The Next 3 and 6 month Milestone Flag = 1, 6,
    BLANK()
    )

     

    and firstly I create the column like this (in the measure above I refers this calculated column):

     

    The high level Upcoming Milestones Flag =
    if [Due Date] >= MONTH(TodayDate) || [% Complete]<1, 1, 0)

    • v-chenwuz-msft's avatar
      v-chenwuz-msft
      Community Support
      4 years ago

      Hi Anonymous ,

       

      If the [Due date] is after the first day of this month, then 1 else 0. The function EOMONTH is used to return the end day of the special month. So we can get the last month and plus 1 to get the first of the special month.

      =>

      if( [Due date] >EOMONTH( TODAY(), -1)+1 ||  [% Complete]<1 ,1,0)

       

      Or you can use the DATE() to get the date you want.

      For example, the first day of TODAY()'s month.

      DATE( Year( TODAY() ), Month( TODAY() ), 1)

      The first day of NEXT month.

      DATE( Year( TODAY() ), Month( TODAY() ) + 1, 1)

      The end day of NEXT month.

      DATE( Year( TODAY() ), Month( TODAY() ) + 2, 1) -1

      ...

      EOMONTH function (DAX) - DAX | Microsoft Docs

      Best Regards

      Community Support Team _ chenwu zhu

       

      If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.