topic Re: Calculating the number of two events which coincide on the same date ("back-to-backs") in DAX Commands and Tips

Calculating the number of two events which coincide on the same date ("back-to-backs")

MJEnnis — Mon, 16 Jan 2023 14:49:45 GMT

I am trying to create a calculated table of something called “back-to-backs”, that is, the number of arrivals that coincide with departures. For instance, if there are checkouts for five rooms at a hotel and on the same day there are three check-ins, then there are three “back-to-backs”. The hotel staff then knows that they must clean at least three of the now vacant rooms (when occupancy is high).

The table with all bookings looks something like this:

ID	Risorsa	Arrivo	Partenza
2890	HOTEL 1	16-Aug-23	23-Aug-23
4160	HOTEL 1	12-Aug-23	26-Aug-23
7156	HOTEL 1	19-Aug-23	26-Aug-23
7506	HOTEL 1	19-Aug-23	26-Aug-23
9015	HOTEL 1	19-Aug-23	26-Aug-23
7035	HOTEL 1	26-Aug-23	05-Sep-23
5044	HOTEL 1	23-Aug-23	06-Sep-23
5044	HOTEL 1	23-Aug-23	06-Sep-23
5044	HOTEL 1	23-Aug-23	06-Sep-23
3504	HOTEL 1	26-Aug-23	09-Sep-23
3503	HOTEL 1	26-Aug-23	09-Sep-23

I have tried to use a measure and then calculated columns to do this, but there is always one step I cannot seem to do with DAX. So, I am now trying with a calculated table. I think I have figured out a way to do it, but something still isn’t working…

The first part of my code works fine.

BacktoBAcks = VAR Tab1 = SELECTCOLUMNS('Lista arrivi', "Resource", 'Lista arrivi'[Risorsa], "Arrive_Date", 'Lista arrivi'[Arrivo], "Depart_Date", 'Lista arrivi'[Partenza])

This returns the same table above without the IDs.

Then the following code gives me the proper calculations of the number of arrivals on the same date of each departure, and vice versa, leaving all other rows blank.

Var Tab2 = ADDCOLUMNS(Tab1, "Arrivals", CALCULATE(COUNTROWS('Lista arrivi'), FILTER( 'Lista arrivi', 'Lista arrivi'[Risorsa] = [Resource] && 'Lista arrivi'[Arrivo] = [Depart_Date] )), "Departures", CALCULATE(COUNTROWS('Lista arrivi'), FILTER( 'Lista arrivi', 'Lista arrivi'[Risorsa] = [Resource] && 'Lista arrivi'[Partenza] = [Arrive_Date] )) )

Risorsa	Arrivo	Partenza	Arrivals	Departures
HOTEL 1	16-Aug-23	23-Aug-23		3
HOTEL 1	12-Aug-23	26-Aug-23		3
HOTEL 1	19-Aug-23	26-Aug-23		3
HOTEL 1	19-Aug-23	26-Aug-23		3
HOTEL 1	19-Aug-23	26-Aug-23		3
HOTEL 1	26-Aug-23	05-Sep-23	4
HOTEL 1	23-Aug-23	06-Sep-23	1
HOTEL 1	23-Aug-23	06-Sep-23	1
HOTEL 1	23-Aug-23	06-Sep-23	1
HOTEL 1	26-Aug-23	09-Sep-23	4
HOTEL 1	26-Aug-23	09-Sep-23	4

So far, so good. At this point I would like to separate the arrivals and departures into two separate tables so that I can remove blanks and duplicates before joining them into one table. I want something like this, so that I can then use basic logic to calculate the “back-to-backs”. Essentially I would just need to add a column that takes the lower of the two values.

Risorsa	Date	Arrivals	Departures
HOTEL 1	26-Aug-23	4	3
HOTEL 1	23-Aug-23	1	3

But when I try to select the columns to perform a join, as below, the counts are all messed up. I even end up getting some departures on dates on which there are none for the same property.

Var Tab3 = DISTINCT(SELECTCOLUMNS(Tab2a, "Resource", [Resource], "Date", [Arrive_Date], "Departures", [Depatures] ))

Departures	Resource	Date
3	HOTEL 1	19-Aug-23
3	HOTEL 1	23-Aug-23
2	HOTEL 1	26-Aug-23
1	HOTEL 1	12-Aug-23
1	HOTEL 1	26-Aug-23
1	HOTEL 1	16-Aug-23

Any ideas why this is happening? Seems like it is recalculating the column based on a different context or something. But this has never happened before. Any help would be much appreciated!!

Re: Calculating the number of two events which coincide on the same date ("back-to-backs")

tamerj1 — Mon, 16 Jan 2023 15:22:00 GMT

Hi MJEnnis

Is "ID" the Room ID? Why for ID 5044 the same record is repeated 3 times?

Re: Calculating the number of two events which coincide on the same date ("back-to-backs")

johnt75 — Mon, 16 Jan 2023 15:29:41 GMT

I think you're overcomplicating things. Create a date table, marked as date table which has relationships to both arrival and departure date. Only one relationship can be active but that's OK. Then create measures like

Num arrivals = CALCULATE( COUNTROWS( 'Table'), USERELATIONSHIP('Date'[Date], 'Table'[Arrival date]) ) Num departures = CALCULATE( COUNTROWS( 'Table'), USERELATIONSHIP('Date'[Date], 'Table'[Departure date]) ) Num back to backs = VAR Arrivals = [Num arrivals] VAR Departures = [Num departures] RETURN IF ( NOT ( ISBLANK ( Arrivals ) ) && NOT ( ISBLANK ( Departures ) ), MIN ( Arrivals, Departures ) )

then you can put the Num back to backs measure into a visual with a column from your date table

Re: Calculating the number of two events which coincide on the same date ("back-to-backs")

tamerj1 — Mon, 16 Jan 2023 18:21:22 GMT

MJEnnis

If not possible to create relationships for any reason you may try

BacktoBAcks = FILTER ( GENERATE ( CROSSJOIN ( VALUES ( 'Lista arrivi'[Risorsa] ), CALENDAR ( MIN ( 'Lista arrivi'[Arrivo] ), MAX ( 'Lista arrivi'[Partenza] ) ) ), VAR CurrentDate = [Date] VAR CurrentResort = 'Lista arrivi'[Risorsa] VAR CurrentResortTable = FILTER ( 'Lista arrivi', 'Lista arrivi'[Risorsa] = CurrentResort ) RETURN ROW ( "Arivals", COUNTROWS ( FILTER ( CurrentResortTable, 'Lista arrivi'[Arrivo] = CurrentDate ) ), "Departures", COUNTROWS ( FILTER ( CurrentResortTable, 'Lista arrivi'[Partenza] = CurrentDate ) ) ) ), [Arivals] > 0 && [Departures] > 0 )

Re: Calculating the number of two events which coincide on the same date ("back-to-backs")

MJEnnis — Mon, 16 Jan 2023 16:35:04 GMT

It is a booking ID. The same booking can include multiple "resources". Like iif a family needed to book three rooms. But the ID shouldn't be necessary for this calculation.

Re: Calculating the number of two events which coincide on the same date ("back-to-backs")

MJEnnis — Mon, 16 Jan 2023 16:48:22 GMT

You are absolutely right as soon as I switched to a calculated table, I felt like I was overcomplicating it. I was actually hoping that doing so would help me better conceptualize a measure. It has helped me clearly understand what needs to occure conceptually, but I still cannot figure out how to do it as measure. I thought I had figured out how to create a new table with the correct calculations, but alas...

What you are proposing is almost excatly what I tried to do with a measure. I have a calendar table. I created a measure for arrivals and one for departures. Then I created a measure that takes the minimum of the two. I can segment that measure by date using the calendar table and by "resource" using the arrivals table. The segmented calculation is correct. However, the aggregates do not add up, because the measure does not filter for "resource". I am doing this a favor for my wife. Her company has many properties and each property has many resources. So when you filter or segment a visual for an individual resource, it gives you the true back-to-backs for that resource on a date or during a period (I would call it "unit type at each property"). But when you visualize two or more resources/properties together, it overcounts the back-to-backs. The measure does not account for the fact that, in my wife's definition, a back-to-back is per resource. This should be aggregated by property and for the whole company, but it cannot just be all the lesser of arrivals versus departures. The measure has to filter by resource. Hope this makes sense.

Re: Calculating the number of two events which coincide on the same date ("back-to-backs")

johnt75 — Mon, 16 Jan 2023 16:53:38 GMT

You could iterate over the different resources like

Num back to backs = SUMX ( VALUES ( 'Table'[Resource] ), VAR Arrivals = [Num arrivals] VAR Departures = [Num departures] RETURN IF ( NOT ( ISBLANK ( Arrivals ) ) && NOT ( ISBLANK ( Departures ) ), MIN ( Arrivals, Departures ) ) )

Re: Calculating the number of two events which coincide on the same date ("back-to-backs")

MJEnnis — Mon, 16 Jan 2023 16:55:12 GMT

Also, knowing how my wife approaches her company's data, I think she will want to have a data table she can "read" and export. 🙂 So a a calculated table may be the best option in the end...

Re: Calculating the number of two events which coincide on the same date ("back-to-backs")

johnt75 — Mon, 16 Jan 2023 17:03:51 GMT

Try

Summary Table = GENERATE ( SUMMARIZE ( 'Table', 'Date'[Date], 'Table'[Resource] ), VAR Arrivals = [Num arrivals] VAR Departures = [Num departures] VAR BackToBack = IF ( NOT ( ISBLANK ( Arrivals ) ) && NOT ( ISBLANK ( Departures ) ), MIN ( Arrivals, Departures ) ) RETURN ROW ( "Arrivals", Arrivals, "Departures", Departures, "Back to back", BackToBack ) )

Re: Calculating the number of two events which coincide on the same date ("back-to-backs")

MJEnnis — Mon, 16 Jan 2023 17:13:00 GMT

Good idea! Produces the correct results when segmented, but not when aggregated. Identical calculations as before...

Re: Calculating the number of two events which coincide on the same date ("back-to-backs")

MJEnnis — Mon, 16 Jan 2023 17:16:48 GMT

I cannot explain this because I do not undertand it. But when I changed one of my draft measures to calculate B2Bs using your code above (iterations), my calculated table started returning the expected values... Was the measure I had before somehow interacting with the calculated table? That makes no sense to me...

Re: Calculating the number of two events which coincide on the same date ("back-to-backs")

MJEnnis — Mon, 16 Jan 2023 17:17:41 GMT

I made no other changes to any dax code. I simply copied and pasted your code over an existing measure (that was not working).

Re: Calculating the number of two events which coincide on the same date ("back-to-backs")

tamerj1 — Mon, 16 Jan 2023 17:45:13 GMT

MJEnnis

Have you tried my proposed code?

Re: Calculating the number of two events which coincide on the same date ("back-to-backs")

MJEnnis — Mon, 16 Jan 2023 18:14:38 GMT

Going to! I got my method to work in the end. But mine is way to cumbersome (and probably burdensome). I just had to finsih what I started as soon as I started getting the right values for no apparent reason... I will try yours, and if it works, I will accept it as a solution regardless of which I use!

Here is mine... 😮

BacktoBacks = VAR Tab1 = SELECTCOLUMNS('Lista arrivi', "Resource", 'Lista arrivi'[Risorsa], "Arrive_Date", 'Lista arrivi'[Arrivo], "Depart_Date", 'Lista arrivi'[Partenza]) Var Tab2 = ADDCOLUMNS(Tab1, "Arrivals", CALCULATE(COUNTROWS('Lista arrivi'), FILTER( 'Lista arrivi', 'Lista arrivi'[Risorsa] = [Resource] && 'Lista arrivi'[Arrivo] = [Depart_Date] )), "Departures", CALCULATE(COUNTROWS('Lista arrivi'), FILTER( 'Lista arrivi', 'Lista arrivi'[Risorsa] = [Resource] && 'Lista arrivi'[Partenza] = [Arrive_Date] )) ) Var Tab3 = DISTINCT(SELECTCOLUMNS(Tab2, "Resource", [Resource], "Date", [Arrive_Date], "Departures", [Departures] )) Var Tab4 = DISTINCT(SELECTCOLUMNS(Tab2, "Resource1", [Resource], "Date", [Depart_Date], "Arrivals", [Arrivals] )) Var Tab3a = FILTER(Tab3, NOT(ISBLANK([Departures]))) Var Tab4a = FILTER(Tab4, NOT(ISBLANK([Arrivals]))) Var Tab5 = UNION(Tab3a, Tab4a) Var Tab6 = ADDCOLUMNS(Tab5, "B2Bs", MINX(FILTER(Tab5, [Resource] = EARLIER([Resource]) && [Date] = EARLIER([Date])), [Departures])) Var Tab7 = DISTINCT(SELECTCOLUMNS(Tab6, "Resource", [Resource], "Date", [Date], "Back-to-backs", [B2Bs])) RETURN Tab7

Re: Calculating the number of two events which coincide on the same date ("back-to-backs")

tamerj1 — Mon, 16 Jan 2023 18:18:46 GMT

MJEnnis

Hold on I discovered a mistake. I will modify the code in the original reply

Re: Calculating the number of two events which coincide on the same date ("back-to-backs")

CNENFRNL — Mon, 16 Jan 2023 19:07:28 GMT

Simple enough

Re: Calculating the number of two events which coincide on the same date ("back-to-backs")

MJEnnis — Mon, 16 Jan 2023 23:12:55 GMT

This works well with the sample I have provided, and could be a good solution for the measure option! Thanks a lot!

But, to be clear, I would ultimately need something like this.

Back2Backs = Var B2BIN = IF( NOT CALCULATE( ISEMPTY( BOOKING ), USERELATIONSHIP ( DATES[Date], BOOKING[Check-Out] ) ), COUNTROWS( BOOKING ) ) Var B2BOUT = IF( NOT ISEMPTY( BOOKING ), CALCULATE( COUNTROWS( BOOKING ), USERELATIONSHIP ( DATES[Date], BOOKING[Check-Out] ) ) ) RETURN MIN(B2BIN, B2BOUT)

As I am only interested in visualizing the last column shown below. The other two measures would be useless clutter.

Your code should produce the right calculations when segmenting by "resources" (what you have labeled "estates") and dates, but as with the measure I have already tried and as with the one johnt75 has suggested, I am doubtful that yours will aggregate properly when it is segmented in other ways. (As explained, most properties have multipe "resources", and so my wife often needs to do the calculation at the property level or company level, and segmenting/filtering the data at these levels has resulted in incorrect calculations so far. At all levels, the calculation needs to be based on the same "resource" and the property level and company level calcuations should be a sum of the respective resource level calculations, not a new calculation across a property or the company.

I will test it out as soon as I get a chance though.

Also, there are currently no relationships between the calendar table and and the bookings table. So I need to check how adding relationships might affect existing measures...

Thanks again!

Re: Calculating the number of two events which coincide on the same date ("back-to-backs")

MJEnnis — Tue, 17 Jan 2023 06:15:00 GMT

tamerj1 have you updated the code? If so, I will try it out today.

Re: Calculating the number of two events which coincide on the same date ("back-to-backs")

tamerj1 — Tue, 17 Jan 2023 06:52:51 GMT

MJEnnis

Yes

Re: Calculating the number of two events which coincide on the same date ("back-to-backs")

MJEnnis — Tue, 17 Jan 2023 10:02:26 GMT

As suspected, it doesn't aggregate and sum properly. So this is just a much "cleaner" way of trying what has already been tried.

If there were a way a filter like this: FILTER(BOOKING, BOOKING[Estate] = EARLIER(BOOKING[Estate]))...