Forum Discussion

RevenNic's avatar
RevenNic
Frequent Visitor
6 years ago

Comparative booking pace

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

Need Help
Tips and Tricks

5 Replies

  • amitchandak's avatar
    amitchandak
    Super User
    6 years ago

    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's avatar
      RevenNic
      Frequent Visitor
      6 years ago

      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

  • v-yingjl's avatar
    v-yingjl
    Community Support
    6 years ago

    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's avatar
      RevenNic
      Frequent Visitor
      6 years ago

      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.

       

  • RevenNic's avatar
    RevenNic
    Frequent Visitor
    6 years ago

    Sorry my table didn't work too well there,

     

    Second attempt as an image: