SWITCH on Conditional DATETIME selection help

[5000]

I believe I am missing something obvious here.  Looking to select a 'PIT Date' (shown in bottom right), then compare that date to other dates (today, a 'create date', and an 'actual date') in order to redefine an order status as a PIT (Point In Time) status.  Goal is to rebuild snapshots of an orderbook at other points in time vs today.

No errors coming up in formula bar, but calculation is not correct
All dates are formatted as "datetime"
Order Line Status is formatted as "text"
The example shown below with CurrentPIT of "6/1/2024" should have blank and "NOT" values in addition to the Shipped and Open

Suggestions appreciated, thank you!

[Anonymous · Accepted solution]

Hi, @5000 

Thanks for Ritaf1983's concern about this issue.

May I ask what your dataset looks like?
Since you didn't give the dataset for conducting the test, I assumed some data for my own test:

In my tests, your Measure is working fine:

Maybe you can refer to my Measure for modification:

PIT Status2 =
VAR CurrentPIT =
    IFERROR ( SELECTEDVALUE ( 'Z_Point In Time'[PIT Date], TODAY () ), TODAY () )
RETURN
    SWITCH (
        TRUE (),
        CurrentPIT = TODAY (), [Order Line Status1],
        [Order Line Status1] = "Open"
            && CurrentPIT > [Create Date], "Open",
        [Order Line Status1] = "Open"
            && CurrentPIT <= [Create Date], "",
        [Order Line Status1] = "Shipped"
            && CurrentPIT > [Actual Date], "Shipped",
        [Order Line Status1] = "Shipped"
            && CurrentPIT <= [Actual Date]
            && CurrentPIT > [Create Date], "Open",
        [Order Line Status1] = "Shipped"
            && CurrentPIT <= [Actual Date]
            && CurrentPIT <= [Create Date], "",
        "NOT"
    )

I have attached the pbix file below for your reference, I hope it helps.

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.

[Ritaf1983]

Hi @5000 

Can you please share a pbix or some dummy data that keep the raw data structure with expected results? It should help us clarify your scenario and test to coding formula.

How to Get Your Question Answered Quickly