Nested if statement with date comparison

[Anonymous]

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!

[Greg_Deckler]

@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]

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]

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.