Forum Discussion
Nested IF - dates
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.
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
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
3 Replies
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
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)
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
