Forum Discussion

jnickell's avatar
jnickell
Helper V
5 years ago

Less than Date operator with Dates

This is probably going to wind up being something simple.  I have two tables that I'm using in a measure. There is only an inactive relationship between the two, and I'm not activating the relationsh...
Greg_Deckler's avatar
Greg_Deckler
Community Champion
5 years ago

jnickell Perhaps try this:

Projected Value in Period = 
    VAR DateInView = MIN('Date'[Date]) 
    VAR MostRecentProjection = 
        MAXX(
            FILTER(ALL('Project Revenue),'Project Revenue'[Project Month] < DateInView),
            'Projected Revenue'[Projection Month]
        )
    RETURN
        MostRecentProjection
jnickell's avatar
jnickell
Helper V
5 years ago

Greg_Deckler 
Scratch that I had an additional filter on my dates that seems to have allowed that to work. Removing the filter on the Axis reintroduced the error. 
So working on the theory that it has something to with "time" values I tried this (forcing the Date values to integers before comparing) and it gets me the result I was expecting.  This seems like a cludge though.  Thoughts on what I should be doing?

 

    VAR DateInView = CONVERT(MIN('Date'[Date]),INTEGER)
    VAR MostRecentProjection = 
        CALCULATE(
            MAX('Projected Revenue'[Projection Month]),
            Convert('Projected Revenue'[Projection Month],INTEGER) < DateInView
        )
    RETURN
        MostRecentProjection