topic Re: Count previous rows based on date and restart counting if meets a criteria in DAX Commands and Tips

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

CTozzi — Thu, 21 May 2020 17:35:45 GMT

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,

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

AlB — Thu, 21 May 2020 19:01:39 GMT

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

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

CTozzi — Wed, 03 Jun 2020 17:08:34 GMT

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.

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

AlB — Wed, 03 Jun 2020 18:47:59 GMT

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