topic Less than Date operator with Dates in DAX Commands and Tips

Less than Date operator with Dates

jnickell — Fri, 04 Jun 2021 14:19:18 GMT

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 relationship.

Table 1 is SQLBI's Dax Calendar

Table 2 is a list of projected values. Because they're projected, the same target month may have multiple values, and I only want to show the projected value from the last month before. (i.e. what'd we say the value was going to be for this month, last month)

My issue is that while my first variable correctly identifies the first of the month on my axis, the variable that determines what the most recent projection month is "prior to this month" is returning the same month, rather than the previous month. My Less than doesn't seem to be working 4/1/2021 isn't "Less than" 4/1/2021".

Is this because my calculate's filter statement is working against the same column that I'm trying to return or does it have something to due with the time values. Both columns are identified as "Date" types.

Projected Value in Period = VAR DateInView = MIN('Date'[Date]) VAR MostRecentProjection = CALCULATE( MAX('Projected Revenue'[Projection Month]), 'Projected Revenue'[Projection Month] < DateInView ) RETURN MostRecentProjection

For May 2021, I my goal was the returned valued to be "4/1/2021". Because May is the last month that any projections exist, June and later work.

This is Table 2 for the "May and June" values

Projection Month

Wednesday, July 1, 2020

Saturday, August 1, 2020

Tuesday, September 1, 2020

Monday, February 1, 2021

Monday, March 1, 2021

Thursday, April 1, 2021

Saturday, May 1, 2021

Re: Less than Date operator with Dates

Greg_Deckler — Fri, 04 Jun 2021 14:35:18 GMT

@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

Re: Less than Date operator with Dates

jnickell — Fri, 04 Jun 2021 14:41:02 GMT

@Greg_Deckler Thanks for the reply. I tried that and it introduces the same problem, and for June and later it shows 6/1/2021 instead of the May 2021 value I would want to use.

Re: Less than Date operator with Dates

jnickell — Fri, 04 Jun 2021 14:59:54 GMT

@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

Re: Less than Date operator with Dates

Greg_Deckler — Fri, 04 Jun 2021 19:32:50 GMT

@jnickell Going to need sample data to mock this up. Here is my standard "more information" macro:

Sorry, having trouble following, can you post sample data as text and expected output?
Not really enough information to go on, please first check if your issue is a common issue listed here: https://community.powerbi.com/t5/Community-Blog/Before-You-Post-Read-This/ba-p/1116882

Also, please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

The most important parts are:
1. Sample data as text, use the table tool in the editing bar
2. Expected output from sample data
3. Explanation in words of how to get from 1. to 2.

Re: Less than Date operator with Dates

jnickell — Fri, 04 Jun 2021 20:58:42 GMT

@Greg_Deckler I wound up deleting my test visual and recreating it and the issue didn't represent. Don't know if I had a filter I wasn't seeing or a bad cache of something. Don't have an explanation for what I was seeing, but it's not happening now. Thanks for your responses.