Why does operator <= (less than or equal) not return expected result when date is variable?

Question

Problem: DAX is not returning expected result in if statement with operator when date is a variable.

Background: I've checked to make sure that there is no date or time difference. Dates are ALL in format (1stday of month, 12:00:00AM). I have 2 years (2018 & 2019: 24 distinct dates). I've tried to use the <= operator in a calculated column to determine if a date is "Before/Same" to a variable date or "After". I've tried to combine < filter and = filter with || (or), same result.

TestColumn = 
var CurrentDate = [Current_Period]
return
IF(
    ValuesTable[Month-Year] <= CurrentDate,
    "Before/Same", "After"
)

FYI: The [Current_Period] Measure, this works fine, returns one date as expected

Current_Period = 
VAR ThisActMonth =
    CALCULATE (
        DISTINCT ( Fact_FX[Month-Year] ),
        Fact_FX[bool_Latest_FX_Act] = TRUE ()
    )
RETURN
    ThisActMonth

I would expect that for every row where the Month-Year (Date) <= the result would be "Before/Same"; however, The result I currently get is:

"Before/Same" only where ValuesTable[Month-Year] = CurrentDate CurrentDate = April 2019

"After" all other Months (23)

Please Help!

Are you getting an error message? If not, what is it returning that you don't expect? — Alexis Olson
@AlexisOlson No Error Message. I would expect that for each row where Month-Year is before/same as CurrentDate return in TestColumn would be "Before/Same" and for each where Month-Year is after CurrentDate to return "After". Currently it is returning "Before/Same" for March 2019 only(CurrentDate = March 2019); for all other 23 months it returns "After". Thanks Alexis — user3845582

Alexis Olson Alexis Olson · Accepted Answer · 2019-04-10T14:33:03

What's happening is that you are evaluating your Current_Period measure within the row context of the calculated column, so the measure doesn't see the row with Fact_FX[bool_Latest_FX_Act] = TRUE () except in the April row.

The solution is to calculate the Current_Period outside of that evaluation context and there are multiple possible approaches. A quick solution would be to remove row context using the ALL function inside of your CALCULATE:

Current_Period = 
VAR ThisActMonth =
    CALCULATE (
        DISTINCT ( Fact_FX[Month-Year] ),
        ALL( Fact_FX ),
        Fact_FX[bool_Latest_FX_Act] = TRUE ()
    )
RETURN
    ThisActMonth

Why does operator <= (less than or equal) not return expected result when date is variable?

1 Answers