Unable to create a column based on a date comparison with measure

[CDantas]

Hi everyone, 

I've looked up several post similar to mine, however none addressed accordingly my problem.

I'm trying to define the status ("Delayed" or "On Track") of a 'Request' based on a comparison of its due date and the date selected by user in slicer.

To do so, I've created the measure 'Actual Date' which should be the end of the month/year chosen by the user:

Actual Date = EOMONTH(MAXX(ALLSELECTED(Sprints);Sprints[Date]);0)

The measure seems to work properly.

Then, I created a calculated column ('Status') based on the following: 

Status = IF('Requests Dates'[Due Date]<[Actual Date];"Delayed";"On Track")

The issue is that all 'Requests' are evaluated as "Delayed" even when they are not. As shown in the image below:

I've double checked data types, but they seem right.

I figured out the measure 'Actual Date' is considered 31/12/2025, which is the last row of 'Sprint' Table, in the calculation of the Statement of 'Status'. This is probably the cause of the issue, however I'm not able to solve it.

To illustrate better:

Power BI Project

Data in xls

Important note: I cannot delete the relations of 'Sprint" Table because the rest of the report depends on it.

 Thanks in advance,

Caio

[v-shex-msft]

Hi @CDantas,

AFAIK, current power bi not support to create dynamic calculate column/table based on slicer/filter, please use measure to instead.

In addition, if you drag dynamic measure to calculate column/table, its row contents will be fixed as whole table and calculate result will return static value.

Regards,

Xiaoxin Sheng

[CDantas]

Hi @v-shex-msft,

not sure I understand what you meant by:

---

 please use measure to instead.

---

I wasn't able to create a measure in which I could evaluate if the 'Request' is delayed or not. Is that possible?

Any other solution that make me view (and count) the delayed requests based on the Year/Month chosen in the report would be great.

Thanks for you attention,

Caio

[AlB]

Hi @CDantas

I think you are mixing up concepts here. You are using a measure ( [Actual Date]) inside a calculated column. The measure relies on filter context, which is absent in a calculated column. Without that filter context, [Actual Date] takes the full 'Sprints' table into account and thus always returns 31/12/2025, the latest (max) date in 'Sprints'. That is why everything comes out as "Delayed".

[CDantas]

Thanks @AlB, it makes total sense, however I could not find a way to fix it!