Solved: Re: Calculating the number of two events which coi... - Page 2

MJEnnis · ‎01-16-2023

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 · ‎01-16-2023

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

View solution in original post

tamerj1 · ‎01-17-2023

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

View solution in original post

tamerj1 · ‎01-16-2023

@MJEnnis

Have you tried my proposed code?

MJEnnis · ‎01-16-2023

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