Boolean if order placed within 24 hr rolling period from last order

[ruesaint_denis]

Hi guys, 

I have a rather complicated one. I would like to create a calculated column that has TRUE/FALSE if a user's order was placed within a rolling 24 HR time period from the last order, and the status is not "Canceled" : 

User Id	Order Created At	Status	24HR rolling
10103	3/2/21 12:00	CANCELED	False
10137	6/30/21 8:00	COMPLETED	True
10137	6/29/21 22:00	Transferring	True
10161	4/3/21 22:00	CANCELED	False
10161	4/3/21 18:00	PENDING	True
10161	4/3/21 12:00	COMPLETED	True

Would anyone know how to do this? 

Thank you, 

Denisse

[v-yanjiang-msft · Accepted solution]

Hi @ruesaint_denis ,

According to your description, I modify your sample data like this:

In my understanding, for user ID 10161, the last created order is "4/4/21 16:00:00", then the time "4/3/21 12:00:00" is over 24 hours for the last created order "4/4/21 16:00:00", so it's  value should be false, others in my sample should be true, right? If it is like this, I modify @VahidDM 's formula like this:

24HR rolling =
VAR _LAST =
    CALCULATE (
        MAX ( 'Table'[Order Created At] ),
        ALLEXCEPT ( 'Table', 'Table'[User Id] )
    )
VAR _NOW = 'Table'[Order Created At]
VAR _HR =
    DATEDIFF ( _NOW, _LAST, HOUR )
RETURN
    IF ( _HR < 24 && 'Table'[Status] <> "CANCELED", TRUE (), FALSE () )

If you still have other problems, please feel free to let me know.

I attach my sample below for reference.

Best Regards,
Community Support Team _ kalyj

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[VahidDM]

Hi @ruesaint_denis 

Try this to add a new column with DAX:

24HR rolling =
VAR _MAXT =
    CALCULATE (
        MAX ( 'Table'[Order Created At] ),
        ALLEXCEPT ( 'Table', 'Table'[User Id] )
    )
VAR _MINT =
    CALCULATE (
        MIN ( 'Table'[Order Created At] ),
        ALLEXCEPT ( 'Table', 'Table'[User Id] )
    )
VAR _HR =
    DATEDIFF ( _MINT, _MAXT, HOUR )
RETURN
    IF ( _HR < 24 && 'Table'[Status] <> "CANCELED", TRUE (), FALSE () )

output:

If this post helps, please consider accepting it as the solution to help the other members find it more quickly.
Appreciate your Kudos!!
LinkedIn: www.linkedin.com/in/vahid-dm/