Forum Discussion

5000's avatar
5000
New Member
1 year ago
Solved

SWITCH on Conditional DATETIME selection help

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!

Need Help
  • Anonymous's avatar
    Anonymous
    1 year ago

    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.

2 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    1 year ago

    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.