Creating a measure that considers two separate time periods (purchase and travel dates)

[PBI12345]

Hi,

I have a dataset that involves passengers for a transport service. In this dataset, there are three key fields:

• Purchase Date
• Travel Date
• Passenger Count

My goal is to show two three key values:

1. The current number of booked passengers for a future travel date (ie. Travel Date = 01DEC25)
   
   
2. How that comparable travel date from the year prior performed (ie. Travel Date = 02DEC24 [-364 days instead of SAMEPERIODLASTYEAR to compare equivalent DOW not necessarily exact date])
   
   
3. How that comparable travel date from the year prior was performing as at (AsAt) this same point last year (ie. Travel Date = 02DEC24 , and Purchase Date <= TODAY-364 days)

Points (1) and (2) are easily solved, but I've tried everything for point (3) without success. Ideally my output would show:

Travel Date	Passengers (AsAt)	Passengers (Final)
2/12/2024	200	300
1/12/2025	195	195

I have simplified this for clarity, but purchase date should also be added as a row and not break the logic. 

To date, most of my attempts have used DATEADD or SAMEPERIODLASTYEAR logic unsuccessfully. The closest I got was to have the AsAt values pull through, but as a duplicate value in the previous year and not in the current year as intended. 

I would love someone's assistanace please! I have spent a long time on this. Many thanks in advance. 

[DataNinja777]

Hi @PBI12345 ,

To calculate the number of passengers for a future travel date as of the same relative point in time from the previous year, we need three key measures: the final number of passengers for a given travel date, the number of passengers for a comparable travel date in the previous year (shifted by 364 days), and the number of passengers for that comparable travel date as of the same relative purchase date cut-off.

The first measure, Passengers (Final), is straightforward as it simply sums the passenger count:

Passengers (Final) =
SUM('Table'[Passenger Count])

For the comparable travel date from the previous year, we shift the travel date by 364 days:

Passengers (Comparable Travel Date) =
CALCULATE(
    SUM('Table'[Passenger Count]),
    'Table'[Travel Date] = MAX('Table'[Travel Date]) - 364
)

The most complex part is retrieving the number of passengers for the comparable travel date while ensuring that the purchase date does not exceed the same relative point in time from the previous year. This can be done by filtering both the travel date and purchase date accordingly:

Passengers (AsAt) =
VAR TravelDatePriorYear = MAX('Table'[Travel Date]) - 364
VAR PurchaseDateCutoff = TODAY() - 364

RETURN
CALCULATE(
    SUM('Table'[Passenger Count]),
    'Table'[Travel Date] = TravelDatePriorYear,
    'Table'[Purchase Date] <= PurchaseDateCutoff
)

This ensures that the Passengers (AsAt) measure correctly reflects the number of passengers booked for the comparable travel date but only includes purchases made up to the same cutoff point from the prior year. This approach dynamically adjusts based on the selected travel date and maintains a proper year-over-year comparison while aligning with the same day of the week.

Best regards,

[PBI12345]

Hi @DataNinja777 ,

Thanks for your reply and I really appreciate your help!

This approach works for calculating the Passengers (AsAt) at a total level, but it does not evaluate correctly when purchase date is included in the table. Using my example data from above, the measure you've provided would lead to "200" being shown against every value of purchase date.

See below for an example using the above sample data. Col 4 is the expected output, and col 5 is the output with the meausure you've provided:

Travel Date	Purchase Date	Passengers (TY)	Passengers (LY, ASAT) Expected	Passengers (LY, ASAT) Current	Passengers (Final)
1/12/2025	1/01/2025	100	75	200	75
1/12/2025	1/02/2025	50	75	200	75
1/12/2025	1/03/2025	45	50	200	50
1/12/2025	1/04/2025	0	0	200	25
1/12/2025	1/05/2025	0	0	200	10
1/12/2025	1/06/2025	0	0	200	10
1/12/2025	1/07/2025	0	0	200	10
1/12/2025	1/08/2025	0	0	200	5
1/12/2025	1/09/2025	0	0	200	10
1/12/2025	1/10/2025	0	0	200	10
1/12/2025	1/11/2025	0	0	200	10
1/12/2025	1/12/2025	0	0	200	10
		195	200	200	300

Thanks again for the assistance.