Issue with Referencing Date Column in Calculated Column for Date Comparison

[DJims86]

Hi everyone,

I’m working on a Power BI report and encountering an issue when trying to create a calculated column that categorizes data based on a specific cutoff date. My goal is to label rows as either "Before Cutoff" or "After Cutoff" based on whether the Date column is before or after February 28, 2025.

Here’s the DAX expression I wrote for the calculated column:

DAX

BackgroundColorCategory =

IF( 'IF_Enrollment'[Date] <= DATE(2025, 2, 28), "Before Cutoff", "After Cutoff" )

**I also have a field called "ProjActual" that provides the projected vs. actual (or before vs. after cutoff) in a more dynamic way.

 

The Issue:

When I try to use this DAX formula, it doesn't recognize the Date column from the same table ('IF_Enrollment'). I am not sure why the Date column is not being recognized, as it exists in the same table as the calculated column. The goal is to compare the Date value for each row to a specific cutoff date and categorize it accordingly.

What I’ve Tried:

• I’ve confirmed that the Date column exists in the same table ('IF_Enrollment'), so there should be no issues referencing it directly.

• I’ve tried creating a simple calculated column to ensure the Date column is being recognized, and that worked correctly.

  What I Need Help With:

  • Why is the Date column not being recognized in the calculated column despite being part of the same table?

  • How can I correctly reference the Date column for the date comparison in this calculated column?

    TL;DR

    I want to make the area to the left of the line to be one color in the area chart and a different color on the right, allowing me to remove my attempted explanation on the top-right of the screen.

    

    Any help or insights would be greatly appreciated!

    Thank you in advance for your support.

    AreaChartColorChanges.pbix

[lbendlin · Accepted solution]

see attached

[DJims86]

Thank you very much for your help with this. I did have one more question:

When I click on 2025, for example, to view that data, I can see a gap between February and March that I will need to fill. Is there a way to close this gap in the visualization?

[lbendlin]

yes, but you need to indicate in which direction - extend actuals to the right, or extend projected to the left?

[DJims86]

I would need the Actual (blue) to move to the right to meet Projected at March.

[lbendlin]

This is the best I can do. Still a one day gap.

Act = 
var pd = minx(filter(ALL(IF_Enrollment),[ProjActual]="Projected"),[Date])
return SWITCH(TRUE(),
max(IF_Enrollment[Date])=pd-1,CALCULATE(sum(IF_Enrollment[Enrollment]),IF_Enrollment[ProjActual]="Projected"),
max(IF_Enrollment[Date])<pd,CALCULATE(sum(IF_Enrollment[Enrollment]),IF_Enrollment[ProjActual]="Actual"))