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',Today()) OR [% Complete]<1 then
    if [Due Date]<=dateadd('month',3,DATETRUNC('month',Today())) then 3
    elseif [Due Date]<=dateadd('month',6,DATETRUNC('month',Today())) then 6
    END

END

 

so, how can I create thesame calculation in Power BI? I know that we have DATEADD() function in PBI, as for DATETRUNC we can use MONTH() (and other options), the problem is in the combining of the IFs statements.

 

Finally, I would like to use DAX fior it, but maybe the Power Query can help?

 

Thanks for your help in advance!

3 Replies

  • 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.