DAX Dates Remaining

[AndrewKalungi]

For DAX calculations, could you please advice how I can calc a column to show number of days it takes for close an issue if I have an initial date and due date. When it approaches 5 days to the due date, the shows a red color, 15 days to due date yellow, 30 days to due date should be green

[Deku · Accepted solution]

Adjust the hex codes to the colours you like then apply this measure as condition formatting rule

Measure=

Var diff = Datediff( table[due date], today(), day)

Var green = "#aaaaaa"

Var yellow = "#aaaaaa"

Var red = "#aaaaaa"

Return

Switch(

True,

Diff <= 5, red,

Diff <= 15, yellow,

Diff <=30, green

)

[AndrewKalungi]

Thank you so much. This really helped. 

[bhanu_gautam · Accepted solution]

@AndrewKalungi You can create a calculated column to determine the number of days between the initial date and the due date.

DaysToClose = DATEDIFF([InitialDate], [DueDate], DAY)

You can use a calculated column to determine the color based on the number of days remaining until the due date.

DueDateColor =
SWITCH(
TRUE(),
[DaysToClose] <= 5, "Red",
[DaysToClose] <= 15, "Yellow",
[DaysToClose] <= 30, "Green",
"No Color"
)

Go to the visualization where you want to apply the conditional formatting.
Select the field you want to format.
Choose "Conditional Formatting" and select "Background Color" or "Font Color".
Use the DueDateColor column to set the color rules.

[Deku · Accepted solution]

Adjust the hex codes to the colours you like then apply this measure as condition formatting rule

Measure=

Var diff = Datediff( table[due date], today(), day)

Var green = "#aaaaaa"

Var yellow = "#aaaaaa"

Var red = "#aaaaaa"

Return

Switch(

True,

Diff <= 5, red,

Diff <= 15, yellow,

Diff <=30, green

)