Comparative booking pace

[RevenNic]

Hi Guys,

I am trying to build a measure that can reference bookings placed for a particular month in comparison to bookings placed in the same period last year, for the same arrival month last year. In the below example I have listed some bookings placed in May 2019 and May 2020, for June 2019 and 2020. I want a table by arrival month, and a filter (slicer) as places day date so I can adjust this to see the pace of bookings placed against same time last year. Hope that makes sense. I have a calendar table attached to the arrival date.

 surname booking placed booking staying nights revenue
smith 1-May-19 1-Jun-19 1 100
jones 1-May-19 2-Jun-19 2 200
thomas 2-May-19 1-Jun-19 1 250
thompson 2-May-19 1-Jun-19 3 475
barney 2-May-19 2-Jun-19 2 200
dickins 2-May-19 3-Jun-19 1 100
omorond 1-May-19 3-Jun-19 4 750
armitage 1-May-20 1-Jun-20 2 250
aitken 1-May-20 1-Jun-20 1 95
root 1-May-20 1-Jun-20 1 30
bone 2-May-20 1-Jun-20 2 140
agrinth 2-May-20 2-Jun-20 1 400
singh 2-May-20 2-Jun-20 1 200
wong 2-May-20 2-Jun-20 2 620
tui 2-May-20 3-Jun-20 1 150
pang 2-May-20 3-Jun-20 3 200
mather 2-May-20 3-Jun-20 1 180
rooks 2-May-20 4-Jun-20 1 240

[v-yingjl]

Hi @RevenNic ,

Not certain what is your expected value to calculate, the same period last year revenue based on booking placed date?

If so, try like this measure:

Same Period Last Year =
CALCULATE (
    SUM ( 'Table'[revenue] ),
    SAMEPERIODLASTYEAR ( 'Table'[booking placed] )
)

If not, could you please share more details or expected output for further discussion?

Best Regards,
Yingjie Li

If this post helps then please consider Accept it as the solution to help the other members find it more quickly.

[RevenNic]

Hi @v-yingjl 

Thank you for trying, formula didn't work. I think it's because I have so many date columns.

My date table is connected to <arrival date> in my bookings table.

Other dates in my table are:

<date placed>

<date cancelled> only has data when a booking is cancelled

<departure date>

I want to see TOTAL REVENUE that was booked during a <date placed> date range for an <arrival date> date range and compare it with same time last year over both ranges.

So:

Revenue for bookings that were <date placed> 1-may-2020 - 13-may-2020 which are <arrival date> 1-june-2020 - 30-june-2020

Compare in a second column revenue for bookings that were <date placed> 1-may-2019 - 13-may-2019 which are <arrival date> 1-june 2019 - 30-june-2019

And same for 2018, 2017 etc..

Hope that helps.

[amitchandak]

@RevenNic , if you date calendar attached start date. Try formula like

MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(Table[Date]))
last year MTD (complete) Sales =  CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(ENDOFMONTH(dateadd(Table[Date],-12,MONTH))))
last MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(dateadd(Table[Date],-1,MONTH)))
last MTD (complete) Sales =  CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(ENDOFMONTH(dateadd(Table[Date],-1,MONTH))))
last year MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(dateadd(Table[Date],-12,MONTH)))
Year behind Sales = CALCULATE(SUM(Sales[Sales Amount]),dateadd(Table[Date],-1,Year))
Month behind Sales = CALCULATE(SUM(Sales[Sales Amount]),dateadd(Table[Date],-1,Month))
last QTR same Month (complete) Sales =  CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(ENDOFMONTH(dateadd(Table[Date],-1,Qtr))))
MTD (Year End) Sales =  CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(ENDOFYEAR(Table[Date])))
MTD (Last Year End) Sales =  CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(ENDOFYEAR(dateadd(Table[Date],-12,MONTH),"12/31")))

[RevenNic]

Thanks @amitchandak I'll give it a go and let you know. It's 8pm here so may not get an answer back to you before bed. Really appreciate the speedy response 🙂

Nic

[RevenNic]

Sorry my table didn't work too well there,

Second attempt as an image: