comparison this year to STLY - but with multiple date columns to be looked at. (travel)

[Anonymous]

Hi, 

I have a SQL table where i have a lot of information regarding reservations from 2022 until today. 

What i want to do is create a Power BI dashboard where i can compare the amount of reservations made this year, versus the same time last year for specific arrival weeks. And also see for which day of the week these people would arrive. 

I tried using some dax formulas, but noticed that when I want to compare 2/1/2023 for example (a monday), it would compare to 2/1/2022 (a sunday). However, I want to compare mondays with Mondays as these have great influence on performance. 

The end goal is to have a slicer where i can select a date range for 2023 (e.g 2/1/2023 until 14/1/2023), and then see how many reservations were made for which arrival weeks, and compare that to the same time last year (3/1/2022 until 15/1/2023), for the same arrival weeks (not influenced by year). 

I've added a screenshot above how the data looks , and on the bottom what i would want the output to be.
example on top of how the data looks, bottom is what i want as output

[Anonymous]

Hi  @Anonymous ,

Here are the steps you can follow：

1. Create calculated table.

Date =
CALENDAR(
    MIN('Table'[CreationDate]),
    MAX('Table'[CreationDate]))

2. Create measure.

reservations_ty =
var _mindate=MINX(ALLSELECTED('Date'),'Date'[Date])
var _maxdate=MAXX(ALLSELECTED('Date'),'Date'[Date])
return
COUNTX(
    FILTER(ALL('Table'),
    'Table'[CreationDate]>=_mindate&&'Table'[CreationDate]<=_maxdate&&'Table'[ArrivalDayofWeek]=MAX('Table'[ArrivalDayofWeek])&&'Table'[ArrivalWeekNum]=MAX('Table'[ArrivalWeekNum])),[ReservationID])

reservations_stly =
var _mindate=MINX(ALLSELECTED('Date'),'Date'[Date])
var _maxdate=MAXX(ALLSELECTED('Date'),'Date'[Date])
return
IF(
    [revenue_stly]<>BLANK(),
COUNTX(
    FILTER(ALL('Table'),    YEAR('Table'[CreationDate])=YEAR(_mindate)-1&&'Table'[ArrivalWeekNum]=MAX('Table'[ArrivalWeekNum])&&'Table'[ArrivalDayofWeek]=MAX('Table'[ArrivalDayofWeek])),[ReservationID]),BLANK())

revenue_stly =
var _mindate=MINX(ALLSELECTED('Date'),'Date'[Date])
var _maxdate=MAXX(ALLSELECTED('Date'),'Date'[Date])
return
SUMX(
    FILTER(ALL('Table'),
    'Table'[CreationDate]>=_mindate&&'Table'[CreationDate]<=_maxdate&&'Table'[ArrivalDayofWeek]=MAX('Table'[ArrivalDayofWeek])&&'Table'[ArrivalWeekNum]=MAX('Table'[ArrivalWeekNum])),[revenue])

revenue_stly_last =
var _mindate=MINX(ALLSELECTED('Date'),'Date'[Date])
var _maxdate=MAXX(ALLSELECTED('Date'),'Date'[Date])
return
IF(
    [revenue_stly]<>BLANK(),
SUMX(
    FILTER(ALL('Table'),   YEAR('Table'[CreationDate])=YEAR(_mindate)-1&&'Table'[ArrivalWeekNum]=MAX('Table'[ArrivalWeekNum])&&'Table'[ArrivalDayofWeek]=MAX('Table'[ArrivalDayofWeek])),[revenue]),BLANK())

3. Result:

Best Regards,

Liu Yang

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

[Anonymous]

Hi @Anonymous , 

What do you mean with revenue_stly_last? I would expect a revenue and then revenue_stly column. 

also i tried your example by plugging in differnet numbers but then it didn't seem to work. See PBI file added. 


The output what i expect is below

ReservationID	CreationDate	ArrivalDate	ArrivalWeekNum	ArrivalDayofWeek	Hotel	revenue
book-001	03/01/2022	01/02/2022	5	Tuesday	paris	€ 100,00
book-002	03/01/2022	01/02/2022	5	Tuesday	paris	€ 487,00
book-003	03/01/2022	03/02/2022	5	Thursday	paris	€ 914,00
book-004	03/01/2022	01/02/2022	5	Tuesday	paris	€ 654,00
book-005	03/01/2022	01/02/2022	5	Tuesday	london	€ 122,00
book-006	03/01/2022	03/02/2022	5	Thursday	london	€ 607,00
book-007	03/01/2022	04/02/2022	5	Friday	london	€ 999,00
book-008	04/01/2022	04/02/2022	5	Friday	london	€ 591,00
book-009	04/01/2022	04/02/2022	5	Friday	london	€ 230,00
book-010	04/01/2022	04/02/2022	5	Friday	london	€ 178,00
book-011	04/01/2022	04/02/2022	5	Friday	paris	€ 282,00
book-012	04/01/2022	04/02/2022	5	Friday	london	€ 534,00
book-023	02/01/2023	31/01/2023	5	Tuesday	london	€ 688,00
book-024	02/01/2023	03/02/2023	5	Thursday	london	€ 178,00
book-025	02/01/2023	03/02/2023	5	Tuesday	london	€ 921,00
book-026	02/01/2023	03/02/2023	5	Thursday	london	€ 631,00
book-027	02/01/2023	03/02/2023	5	Thursday	london	€ 832,00
book-028	03/01/2023	03/02/2023	5	Thursday	paris	€ 413,00
book-029	03/01/2023	04/02/2023	5	Friday	paris	€ 324,00
book-030	03/01/2023	04/02/2023	5	Friday	paris	€ 868,00
book-031	03/01/2023	04/02/2023	5	Friday	london	€ 518,00
book-032	03/01/2023	04/02/2023	5	Friday	london	€ 592,00
book-033	03/01/2023	04/02/2023	5	Friday	london	€ 762,00

creationdate	WeekNum	ArrivalDayofWeek	hotel	reservations_ty	reservations_ly	revenue_ty	revenue_ly
02/01/2023	5	Tuesday	london	2	1	€ 1.609,00	€ 122,00
02/01/2023	5	Thursday	london	3	1	€ 1.730,00	€ 607,00
02/01/2023	5	Friday	london	0	1	€ 0,00	€ 999,00
02/01/2023	5	Thursday	paris	0	1	€ 0,00	€ 914,00
02/01/2023	5	Tuesday	paris	0	3	€ 0,00	€ 1.241,00
03/01/2023	5	Thursday	paris	1	0	€ 413,00	€ 0,00
03/01/2023	5	Friday	london	3	4	€ 1.872,00	€ 1.533,00
03/01/2023	5	Friday	paris	2	1	€ 1.192,00	€ 282,00