Find the Min Date of consecutive Actions prior to 1 specific Action?

[alya1]

Hi All,

I'm not sure if this is possible but I am hoping to pull 2 dates from the below datatable:

Customer ID, Action, Date  
111, A, 1/1/2020
111, B, 1/2/2020
111, C, 1/3/2020
111, D, 1/5/2020

111, B 10/10/2020

111, D, 1/1/2021
111, E, 1/2/2021
111, D, 10/10/2021
111, F, 1/1/2022
111, G, 1/2/2022 ... more customers and more actions

First date that I hope to pull is max date where status = F. This is easy to do.
But the second date I hope to pull is min date where status = either C, D, or E that is consecutive with each other and right prior to F. 
In the example table above, max date = 1/1/2022 and min date = 1/1/2021. 

It wouldn't be 1/3/2020 because although it is consecutive, it is not right prior to F (broken up by an Action B). 

Thank you very much! 

[Ray_Minds]

Hi @alya1 

Find the Min Date of consecutive Actions prior to 1 specific Action? 

I replicated the data which you had provided 

First date that you wanted to pull is max date where status = F 

Max_F_Date =  

CALCULATE( 

    MAX('MinDate'[Date]), 

    'MinDate'[Action] = "F" 

) 

Visual used: Card visual 

Date you want to pull is min date where status = either C, D, or E that is consecutive with each other and right prior to F.   

Min_CDE_Before_F =  

VAR F_Date =  

    CALCULATE( 

        MAX('MinDate'[Date]), 

        'MinDate'[Action] = "F" 

    ) 

VAR Consecutive_CDE = 

    FILTER( 

        'MinDate', 

        'MinDate'[Action] IN {"C", "D", "E"} && 

        'MinDate'[Date] < F_Date 

    ) 

VAR Last_CDE_Date =  

    CALCULATE( 

        MAX('MinDate'[Date]), 

        Consecutive_CDE 

    ) 

RETURN  

CALCULATE( 

    MIN('MinDate'[Date]), 

    'MinDate'[Action] IN {"C", "D", "E"}, 

    'MinDate'[Date] >= Last_CDE_Date, 

    'MinDate'[Date] < F_Date 

) 

Visual used: Card visual 

Best regards,
Ray Minds
http://www.rayminds.com
https://www.linkedin.com/company/rayminds/

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

[OwenAuger · Accepted solution]

Hi @alya1 

I would recommend something like this (PBIX attached):

Max Date = 
CALCULATE (
    MAX ( Data[Date] ),
    Data[Action] = "F"
)

Min Date = 
VAR MaxDate = [Max Date]
VAR MaxNonCDE =
    CALCULATE (
        MAX ( Data[Date] ),
        Data[Date] < MaxDate,
        NOT Data[Action] IN { "C", "D", "E" }
    )
VAR MinDate =
    CALCULATE (
        MIN ( Data[Date] ),
        Data[Date] > MaxNonCDE,
        Data[Date] < MaxDate
    )
RETURN
    MinDate

The Min Date measure finds the maximum date where Action is not C/D/E (MaxNonCDE variable), then finds the minimum date greater than MaxNonCDE but less than MaxDate (MinDate variable). If there is no such date, it should return blank.

Note that any of the CALCULATE filter arguments could be wrapped in KEEPFILTERS if you want these filters to intersect with existing filter context, depending how you are using these measures in the report.

Does this work for you?

[alya1]

beautiful! good way to think of this, thank you 🙂