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

[MJEnnis]

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!!

[MJEnnis · Accepted solution]

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

[tamerj1 · Accepted solution]

@MJEnnis 
This is another solution (simpler and more efficient)

BacktoBacks 2 = 
FILTER ( 
    SUMMARIZE (
        GENERATE (
            CROSSJOIN (
                VALUES ( 'Lista arrivi'[Risorsa] ),
                DISTINCT ( UNION ( VALUES ( 'Lista arrivi'[Arrivo] ), VALUES ( 'Lista arrivi'[Partenza] ) ) )
            ),
            VAR CurrentDate = [Arrivo]
            VAR CurrentResortTable = CALCULATETABLE ( 'Lista arrivi' )
            VAR Back2BackIn =
                COUNTROWS (
                    FILTER ( CurrentResortTable, 'Lista arrivi'[Arrivo] = CurrentDate )
                )
            VAR Back2BackOut =
                COUNTROWS (
                    FILTER ( CurrentResortTable, 'Lista arrivi'[Partenza] = CurrentDate )
                )
            RETURN
                ROW ( "Back2Back", MIN ( Back2BackIn, Back2BackOut ) )
        ),
        [Risorsa], [Back2Back],
        "Date", MAX ( 'Lista arrivi'[Arrivo] )
    ),
    [Back2Back] > 0
)        

[MJEnnis]

Works! Thanks!

[CNENFRNL]

Simple enough

[MJEnnis]

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!

[MJEnnis]

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]))...

[tamerj1]

@MJEnnis 
Apologies, I thought you're looking for a calculated table. I guess I did not read curefully. I'll try to convert it into measures.

[MJEnnis]

Calcuated table is perfect. All the measures that I have tried or have been proposed so far do not manage to filter the booking/arrivi table for the resource. So the sums and aggregates are incorrect. The calculated tables should not have this problem. (Although your totals in the above table seem off...)

[tamerj1]

@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
)

[MJEnnis]

This seems to work to create a calculated table. I would just need to add the actual back to back column and remove the arrivals and departures columns in the output. (How would you do that in your code? Essentially, there should be a new Back-to-back column with the MIN of the other two.)

Will compare yours and mine to see which is more "efficient" for PBI. Thanks a lot!!

[tamerj1]

@MJEnnis 
Here you 

BacktoBAcks = 
SELECTCOLUMNS (
    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 )
            VAR Back2BackIn =
                COUNTROWS (
                    FILTER ( CurrentResortTable, 'Lista arrivi'[Arrivo] = CurrentDate )
                )
            VAR Back2BackOut =
                COUNTROWS (
                    FILTER ( CurrentResortTable, 'Lista arrivi'[Partenza] = CurrentDate )
                )
            RETURN
                ROW (
                    "Back2Back In", Back2BackIn,
                    "Back2Back Out", Back2BackOut,
                    "Back2Back", MIN ( Back2BackIn, Back2BackOut )
                )
        ),
        [Back2Back In] > 0 && [Back2Back Out] > 0
    ),
    "Risora", [Risorsa], 
    "Date", [Date],
    "Back2Back", [Back2Back]
)

[johnt75]

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

[MJEnnis]

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...

[johnt75]

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
        )
)

[MJEnnis]

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.

[johnt75]

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 )
        )
)

[MJEnnis]

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

[MJEnnis]

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...

[MJEnnis]

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

[tamerj1]

Hi @MJEnnis 

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

[MJEnnis]

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.