Count previous rows based on date and restart counting if meets a criteria

[CTozzi]

Hi everyone,

I am on my first steps learning DAX and this is my first post. 

I need to count the number of flights before an aircraft reaches a base. The expected result is on the last column below, as you can see, the aircraft flew 3 flights before reaching a base, then 1 and then 4 flights.
I think the best way would be counting the previous rows, based on date and aircraft nbr. And restart every time it reaches a base.

Tail Nbr	Time of Departure	Departure Airport	Arrival Airport	Base/Not Base	# of flights to reach a Base
CXDR1	2020/01/03 10:39:00	EEVC	EFEB	NotBase
CXDR1	2020/01/03 13:20:00	EFEB	ELEF	NotBase
CXDR1	2020/01/03 15:59:00	ELEF	EFMN	Base	3
CXDR1	2020/01/04 11:27:00	EFMN	EHAM	Base	1
CXDR1	2020/01/04 14:37:00	EHAM	EOWS	NotBase
CXDR1	2020/01/04 16:55:00	EOWS	EHHK	NotBase
CXDR1	2020/01/05 08:00:00	EHHK	EEML	NotBase
CXDR1	2020/01/05 12:35:00	EEML	EFBH	Base	4
CXDR1	2020/01/05 15:05:00	EFBH	EFMH	Base	1
SSLFE	2020/01/01 14:44:00	EHAM	EHHH	Base	1
SSLFE	2020/01/02 07:17:00	EHHH	EEEX	NotBase
SSLFE	2020/01/02 10:02:00	EEEX	EPCS	NotBase
SSLFE	2020/01/03 10:43:00	EPCS	LMMN	NotBase
SSLFE	2020/01/03 13:18:00	LMMN	EBBR	NotBase
SSLFE	2020/01/04 11:46:00	EBBR	ELTE	Base	5
SSLFE	2020/01/05 13:15:00	ELTE	EBBR	NotBase
SSLFE	2020/01/05 17:40:00	EBBR	ESLL	Base	1

Thanks in advance,

[AlB · Accepted solution]

Hi @CTozzi 

I believe the value in the last row of your expected result should be 2 rather than 1

# of flights to reach a Base =
VAR previousbasetime_ =
    CALCULATE (
        MAX ( Table1[Time of Departure] );
        ALLEXCEPT ( Table1; Table1[Tail Nbr] );
        Table1[Base/Not Base] = "Base";
        Table1[Time of Departure] < EARLIER ( Table1[Time of Departure] )
    )
VAR previousNotbasetime_ =
    IF (
        ISBLANK ( previousbasetime_ );
        CALCULATE (
            MIN ( Table1[Time of Departure] );
            ALLEXCEPT ( Table1; Table1[Tail Nbr] );
            Table1[Base/Not Base] = "NotBase";
            Table1[Time of Departure] < EARLIER ( Table1[Time of Departure] )
        )
    )
RETURN
    IF (
        Table1[Base/Not Base] = "Base";
        IF (
            ISBLANK ( previousbasetime_ );
            CALCULATE (
                COUNT ( Table1[Time of Departure] );
                ALLEXCEPT ( Table1; Table1[Tail Nbr] );
                Table1[Time of Departure] <= EARLIER ( Table1[Time of Departure] );
                Table1[Time of Departure] >= previousNotbasetime_
            );
            CALCULATE (
                COUNT ( Table1[Time of Departure] );
                ALLEXCEPT ( Table1; Table1[Tail Nbr] );
                Table1[Time of Departure] <= EARLIER ( Table1[Time of Departure] );
                Table1[Time of Departure] > previousbasetime_
            )
        )
    )

Please mark the question solved when done and consider giving kudos if posts are helpful.

Contact me privately for support with any larger-scale BI needs

Cheers 

[CTozzi]

Thank you @AIB, it worked. The only problem is that I can't run the calculation in my full database. I have to filter two or three months each time, otherwise the excel goes down.

[AlB]

Hi @CTozzi 

Can you share the database? You have to share the URL to the file hosted elsewhere: Dropbox, Onedrive... or just upload the file to a site like tinyupload.com (no sign-up required).

See if this version works better in terms of speed/resources consumption:

# of flights to reach a Base V2 =
VAR previousbasetime_ =
    MAXX (
        FILTER (
            ALL ( Table1[Time of Departure], Table1[Tail Nbr], Table1[Base/Not Base] ),
            Table1[Tail Nbr] = EARLIER ( Table1[Tail Nbr] )
                && Table1[Base/Not Base] = "Base"
                && Table1[Time of Departure] < EARLIER ( Table1[Time of Departure] )
        ),
        Table1[Time of Departure]
    )
VAR previousNotbasetime_ =
    IF (
        ISBLANK ( previousbasetime_ ),
        MINX (
            FILTER (
                ALL ( Table1[Time of Departure], Table1[Tail Nbr], Table1[Base/Not Base] ),
                Table1[Tail Nbr] = EARLIER ( Table1[Tail Nbr] )
                    && Table1[Base/Not Base] = "NotBase"
                    && Table1[Time of Departure] < EARLIER ( Table1[Time of Departure] )
            ),
            Table1[Time of Departure]
        )
    )
RETURN
    IF (
        Table1[Base/Not Base] = "Base",
        IF (
            ISBLANK ( previousbasetime_ ),
            COUNTROWS (
                FILTER (
                    ALL ( Table1[Time of Departure], Table1[Tail Nbr] ),
                    Table1[Tail Nbr] = EARLIER ( Table1[Tail Nbr] )
                        && Table1[Time of Departure] >= previousNotbasetime_
                        && Table1[Time of Departure] <= EARLIER ( Table1[Time of Departure] )
                )
            ),
            COUNTROWS (
                FILTER (
                    ALL ( Table1[Time of Departure], Table1[Tail Nbr] ),
                    Table1[Tail Nbr] = EARLIER ( Table1[Tail Nbr] )
                        && Table1[Time of Departure] > previousbasetime_
                        && Table1[Time of Departure] <= EARLIER ( Table1[Time of Departure] )
                )
            )
        )
    )

Please mark the question solved when done and consider giving kudos if posts are helpful.

Contact me privately for support with any larger-scale BI needs, tutoring, etc.

Cheers