Logical Test with Dates for New Column won't work in every row (help)

[Mbarrera]

Hello

I have a situation that I couldn't solve by myself and have been trying different ways since last week, but i don't seem to make the logical function bring the correct result. 

I have one simple table and I'm trying to have a new calculated column "Forecast" that shows the measure "Vlr_Previsto" if the date is equal or after the 1st of september 2024, and it's previous on that date (else) brings the measure "Vlr_Real".

The measures are the following:

Vlr_Real = CALCULATE(SUM(Tabla1[Valor]),Tabla1[Dato]="Real")

Vlr_Previsto = CALCULATE(SUM(Tabla1[Valor]),Tabla1[Dato]="Previsto")

And the calculates column (I have tried with the  IF function and also with the SWITCH function, but have the same error)

Forecast = 
IF(Tabla1[Date] >= DATE(2024,09,01),[Vlr_Previsto],
    [Vlr_Real]
)

//SWITCH(TRUE(),
//     Tabla1[Date] >= DATE(2024,09,01), [Vlr_Previsto],
//    Tabla1[Date] < DATE(2024,09,01), [Vlr_Real])

The results seem to fail in the second row, because if the date is 1st August 2024 then, it is a previous date, so the "Forecast" column in that row should be blank. If you see in the next rows where the column "Obra" is 7B it does works correctly.

Can you please help me understand why is this happening only in this row and how can I fix this.

Greetings

MB

[Mbarrera · Accepted solution]

Hi @PhilipTreacy 

Thanks for your answer, it helped me to unlock my thoughts.

It's like you said, the code was correct.

My mistake was believing that I can bring the result i wanted with just 2 logical test, but really I needed 4.

I present the code that make the fix:

Forecast = 
SWITCH(TRUE(),
     Tabla1[Dato]="Previsto" && Tabla1[Date] >= DATE(2024,09,01), Tabla1[Valor],
     Tabla1[Dato]="Previsto" && Tabla1[Date] < DATE(2024,09,01), BLANK(),
     Tabla1[Dato]="Real" && Tabla1[Date] >= DATE(2024,09,01), BLANK(),
     Tabla1[Dato]="Real" && Tabla1[Date] < DATE(2024,09,01), Tabla1[Valor]
    )

Thanks

Greetings

MB

[Anonymous]

Hi, @Mbarrera 

It looks like you have found a solution. Could you please mark this helpful post as “Answered”?

This will help others in the community to easily find a solution if they are experiencing the same problem as you.

Thank you for your cooperation!

I hope my suggestions give you good ideas, if you have any more questions, please clarify in a follow-up reply.
Best Regards,
Fen Ling,
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[PhilipTreacy]

@Mbarrera 

In the table you have shown, all dates are 1 August 2024, therefore your Forecat column will always contain whatever the value of [Vlr_real] is.

Why do you say that the 2nd row is wrong when it's the same date aas the 1st and 3rd rows?

What has the column Obra got to do with the calculation?  You haven't mentioned that beofre and it's not in any of the code you are showing.

Phil