Logical Operators on Date Fields ( "<=" works but "<" doesn't )

[Chthonian]

Hello and happy monday folks,

I have a bit of a head-scratcher regarding logical operators and date fields in DAX - if you hadn't guessed from the thread title 🤔.

The crux of it is that when I perform a calculation comparing dates using Less Than ("<") returns a (blank) while using Less Than Or Equal To ("<=") returns a value. I have checked formatting, wrapped my min/max date measures in DATEVALUE() to rule out Text Conversion mixups.....

The Model (Really Simple!) 

 

The DAX:

Opening WIP Value = 
VAR OLOpeningWIP_Open =
    SUMX(
        FILTER (
            Orderlines,
            Orderlines[Created Date] < [Min Selected Date]
                && Orderlines[Orderline Status] IN { "Confirmed", "Delayed", "Processing" }
        ),
        Orderlines[Gross Profit]
    )
VAR OLOpeningWIP_Completed =
    SUMX (
        FILTER (
            Orderlines,
            Orderlines[Created Date] < [Min Selected Date]
                && Orderlines[Completed Date] >= [Min Selected Date]
                && Orderlines[Orderline Status] IN { "Completed" }
        ),
        Orderlines[Gross Profit]
    )
VAR OLOpeningWIP_Cancelled =
    SUMX (
        FILTER (
            Orderlines,
            Orderlines[Created Date] < [Min Selected Date]
                && Orderlines[Status Date] >= [Min Selected Date]
                && Orderlines[Orderline Status] IN { "Cancelled" }
        ),
        Orderlines[Gross Profit]
    )
RETURN
    OLOpeningWIP_Open + OLOpeningWIP_Completed + OLOpeningWIP_Cancelled

Max Selected Date = DATEVALUE( MAX( 'Date'[Date] ) )

Min Selected Date = DATEVALUE( MIN( 'Date'[Date] ) )

The Result:

As soon as I use Orderlines[Created Date] < [Min Selected Date] Dax and I have a falling out and it results in (Blank) for my calculation. Make it less than or equal to the [Min Selected Date] and we are friends again even if the value returned is incorrect 🙄. Any help would be greatly appreciated. 

[Anonymous · Accepted solution]

// Assumption is that [Date] is NOT connected to the fact table.
// If it is, you'll have to add REMOVEFILTERS( Date ) to the
// whole expression after the first line that grabs the min
// selected date in order to be able to perform the calculations
// correctly.

Opening WIP Value =
var __minSelectedDate = [Min Selected Date]
VAR OLOpeningWIP_Open =
    SUMX(
        FILTER(
            Orderlines,
            Orderlines[Created Date] < __minSelectedDate
            &&
            Orderlines[Orderline Status] IN {
                "Confirmed",
                "Delayed",
                "Processing"
            }
        ),
        Orderlines[Gross Profit]
    )
VAR OLOpeningWIP_Completed =
    SUMX(
        FILTER(
            Orderlines,
            Orderlines[Created Date] < __minSelectedDate
            &&
            Orderlines[Completed Date] >= __minSelectedDate
            &&
            Orderlines[Orderline Status] = "Completed"
        ),
        Orderlines[Gross Profit]
    )
VAR OLOpeningWIP_Cancelled =
    SUMX(
        FILTER(
            Orderlines,
            Orderlines[Created Date] < __minSelectedDate
            &&
            Orderlines[Status Date] >= __minSelectedDate
            &&
            Orderlines[Orderline Status] = "Cancelled"
        ),
        Orderlines[Gross Profit]
    )
RETURN
    OLOpeningWIP_Open
    	+ OLOpeningWIP_Completed
    	+ OLOpeningWIP_Cancelled

// This version should be used if you have
// a connection from Date to the fact table.

Opening WIP Value =
var __minSelectedDate = [Min Selected Date]
return
CALCULATE(
	VAR OLOpeningWIP_Open =
	    SUMX(
	        FILTER(
	            Orderlines,
	            Orderlines[Created Date] < __minSelectedDate
	            &&
	            Orderlines[Orderline Status] IN {
	                "Confirmed",
	                "Delayed",
	                "Processing"
	            }
	        ),
	        Orderlines[Gross Profit]
	    )
	VAR OLOpeningWIP_Completed =
	    SUMX(
	        FILTER(
	            Orderlines,
	            Orderlines[Created Date] < __minSelectedDate
	            &&
	            Orderlines[Completed Date] >= __minSelectedDate
	            &&
	            Orderlines[Orderline Status] = "Completed"
	        ),
	        Orderlines[Gross Profit]
	    )
	VAR OLOpeningWIP_Cancelled =
	    SUMX(
	        FILTER(
	            Orderlines,
	            Orderlines[Created Date] < __minSelectedDate
	            &&
	            Orderlines[Status Date] >= __minSelectedDate
	            &&
	            Orderlines[Orderline Status] = "Cancelled"
	        ),
	        Orderlines[Gross Profit]
	    )
	RETURN
	    OLOpeningWIP_Open
	    	+ OLOpeningWIP_Completed
	    	+ OLOpeningWIP_Cancelled,
	    	
	ALL( 'Date' )
	 
)

And, of course, you don't need to wrap anything within DATEVALUE. This, as I mentioned before, wasn't the problem. The problem was most likely in your misunderstading of how context transition works and what an expanded table does.

Best

D

[Chthonian]

@Anonymous my friend, you are a legend! Thanks that worked perfectly. 

And you are right I need to get my ignorance on the topic of context transition under wraps 😌

@EV & @AntrikshSharma thanks for million for your help too. 

[Anonymous]

I'm not a legend, @Chthonian. I'm just a reader of "The Definitive Guide To DAX" by the real LEGENDS - Marco Russo and Alberto Ferrari 🙂 And a faithful one. I've read this book already 5 times from cover to cover.

Best
D

[Chthonian]

@Anonymous I just got my hands on that little gem, along with "Analyzing Data with MS PowerBI, Power Pivot for Excel" I am working through the evaluation context chapter, literally started it last night. I do however thank you for your help! 

[Anonymous]

So then... you're on the right track. The only one real right track 🙂 Good luck!

Best
D

[Anonymous]

Using a measure under FILTER is rarely a good idea (unless you know exactly how context transition works). Such a measure will do a context transition and all values that are less than that date will be excluded from the calculation (which is what you've experienced). What's even more dangerous, you're filtering the EXPANDED TABLE, not the table itself. If you don't have a good understanding of the theory of expanded tables... you're out of luck a bit. Sorry.

I'll try to show you the correct formula in a bit...

Best
D

[Chthonian]

Hi @Anonymous , I had a sinking feeling that Context Transition may be my issue, and being a topic I have not really looked at yet (something I have just started in my studies) I will openly admit that I do not understand it yet 🤔. So I guess I do have some reading to get done and knowledge to absorb.

I appreciate the pointer, and if you get a chance I will really appreciate the formula help. 

[EV]

can you use OR operator?

(Orderlines[Created Date] < [Min Selected Date]) || (Orderlines[Created Date] = [Min Selected Date])

[Chthonian]

Thanks @EV - the OR statement again is not something I need, to get the correct values calculated I need to use less than the [Min Selected Date].

I have a slicer on my report which Slices by "Month & Year" i.e. Jan 2020

[EV]

sorry about that I misread the quesiton.

Got it now. you are tyring to exclude data thats not equal to selected time period. Since its driving from the slicer, I would certainly try to use HASONEVALUE of the slicer fields, this has solved my issues in the past whe comparison conditions are used. 

[AntrikshSharma]

Inside FILTER can you please replace Orderlines with ALLSELECTED ( Orderlines ) and share the findings.

[AntrikshSharma]

<= is an OR condition so if a value is not less than something but is equal to something then some result will be returned.

[Chthonian]

Thanks @AntrikshSharma - the idea here is that I use a pure and simple "Less Than" as i need to exclude any records where the created date equals [Min Selected Date]. The date definitely has dates that are before the [Min Selected Date].