Nested IF - dates

[PC2022]

Hello,

I need to create DAX for date KPI but i am practicing in excel first, so i could create formula in Power BI later.

Green - today is greater than 45 day period leading up to the finish date (please note that it should be 45 day time period, not just one date 45 days prior)

Yellow - today is within 45 days period leading up to the finish date (same note as above, it should be if today falls onto any date within 45 days time period).

Red - today is past finish date

My formula below doesn't seem to be right.

[amitchandak · Accepted solution]

@PC2022 , Create a new column like 

=

var _diff = datediff([finish date], today(), day)

return

Switch(True(),

_diff >=45 , "Green",

_diff > 0 , "Yellow",

"Red"

)

you can use this column in conditional formatting using the field value option

[amitchandak · Accepted solution]

@PC2022 , Create a new column like 

=

var _diff = datediff([finish date], today(), day)

return

Switch(True(),

_diff >=45 , "Green",

_diff > 0 , "Yellow",

"Red"

)

you can use this column in conditional formatting using the field value option

[PC2022]

Thank you! Sorry, I am new to Power BI and a little confused on your answer:

Do you mean to create 2 columns?

1) 

2)

[v-shex-msft · Accepted solution]

Hi @PC2022,

In fact, you can use one calculated column to achieve these, you only need to add a variable to store the first step result and use the switch function to check the diff and return color code.

Color code =
VAR _diff =
    DATEDIFF ( [finish date], TODAY (), DAY )
RETURN
    SWITCH ( TRUE (), _diff >= 45, "Green", _diff > 0, "Yellow", "Red" )

After these steps, you can right-click on your field that needs to be formatted with 'field value' mode based on the above 'color code' column field values.

Apply conditional table formatting in Power BI - Power BI | Microsoft Docs

Regards,

Xiaoxin Sheng