Calculated column for weeks with condition

[harshagraj]

Hello everyone please help me create a calculated column to achieve this with color.

If the end date < 5-10 weeks you milestone then "green"

If the end date < 2-5 weeks that the milestone then "yellow"

If the end date > that milestone then "Red"

"

PS: Both are date columns with hierarchy.

[mahoneypat]

Here's one way to do it.  This measure evaluates the weeks between milestone and enddate, and returns a color value as text.  You can then use that measure in conditional formatting for the background of any of your columns.  Choose Field Value option and pick this measure.

MilestoneColor = var weeksaway = DATEDIFF(MIN(Milestones[EndDate]), MIN(Milestones[Milestone]), WEEK)

var colorcode = SWITCH(TRUE(), weeksaway<0, "Red", weeksaway<5, "Yellow", "Green")

return colorcode

Here is what it will look like

If this works for you, please mark it as the solution.  Kudos are appreciated too.

Regards,

Pat

[amitchandak]

@harshagraj 

Create a column date diff

Diff = datediff([end date],[milestone],day])/7

Create a color measure

Color Date = if(FIRSTNONBLANK([Diff ],0) <5,"green",if(FIRSTNONBLANK([Diff ],0) <3,"yellow","red"))

You can use the same in Conditional formatting. After using "fields".

https://radacad.com/dax-and-conditional-formatting-better-together-find-the-biggest-and-smallest-num...

https://docs.microsoft.com/en-us/power-bi/desktop-conditional-table-formatting#color-by-color-values