topic Re: Nested IF - dates in DAX Commands and Tips

Nested IF - dates

PC2022 — Mon, 20 Jun 2022 13:40:49 GMT

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.

Re: Nested IF - dates

amitchandak — Mon, 20 Jun 2022 13:49:00 GMT

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

Re: Nested IF - dates

PC2022 — Mon, 20 Jun 2022 14:52:35 GMT

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

Do you mean to create 2 columns?

Re: Nested IF - dates

Anonymous — Tue, 28 Jun 2022 08:01:45 GMT

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