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. 

[DataNinja777 · Accepted solution]

Hi @PBI12345 ,

The issue arises because the filter on Purchase Date is not applied correctly when evaluating the measure within the table. The original measure treats Travel Date as a single value, causing duplication across all rows. To resolve this, we need to adjust the measure so that it respects the row context of Purchase Date while still filtering for the correct Travel Date from the previous year.

A revised approach ensures that Passengers (AsAt) considers the same booking window from the previous year for each row. Instead of using a fixed cutoff date like TODAY() - 364, we dynamically set the cutoff based on the row’s Purchase Date. This way, the measure correctly retrieves the number of passengers booked up to that point last year. The corrected DAX measure is:

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

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

With this correction, Passengers (AsAt) now dynamically adjusts based on the Purchase Date in each row, ensuring that the measure properly tracks how the equivalent travel date was performing at the same point last year. The revised output correctly aligns with expectations, eliminating the duplication issue.

Best regards,

[PBI12345]

Thanks - this worked!

[DataNinja777]

Hi @PBI12345 ,

Thanks for your additional question today.  

Of course! Based on your description today, you're trying to compare current year passenger bookings to those from the previous year, but not just based on the Purchase Date—you also want to shift the Travel Date back by 364 days to make sure you're comparing equivalent combinations of travel and booking behavior.

The challenge arises because your existing measure using DATEADD only shifts the Purchase Date, and since Travel Date isn't part of your date table, you can't shift it directly using time intelligence functions like SAMEPERIODLASTYEAR or DATEADD. However, you can still achieve your goal using a row-level filter that manually shifts both dates by 364 days.

Here’s a DAX measure that does exactly that:

Passengers LY =
VAR PurchaseDateCurrent = SELECTEDVALUE('DateTable'[Purchase Date])
VAR TravelDateCurrent = MAX('Table'[Travel Date])

RETURN
CALCULATE(
    SUM('Table'[Passengers]),
    FILTER(
        ALL('Table'),
        'Table'[Purchase Date] = PurchaseDateCurrent - 364 &&
        'Table'[Travel Date] = TravelDateCurrent - 364
    )
)

This measure dynamically looks at the current row’s Purchase Date and Travel Date context, subtracts 364 days from each, and then filters the fact table for rows that match those adjusted dates. The result is a last-year comparison that aligns both booking and travel behavior for equivalent days, not just based on calendar years but the booking window as well. This approach works even without a separate date table for Travel Date, and you don't necessarily need two date tables unless you want to build out more complex visuals or slicers.

Best regard,

[Anonymous]

Hi @PBI12345 ,

Please review the following links, you can refer the solution to get it:

Solved: How to get last year's same period sales on the sa... - Microsoft Fabric Community

Last Year =
VAR cur_ym =
    SELECTEDVALUE ( 'Table 2'[YYYY-MMM] )
VAR cur_year =
    INT ( RIGHT ( cur_ym, 4 ) )
VAR _month =
    CALCULATE ( MONTH ( MAX ( 'Table 2'[Date] ) ), 'Table 2'[YYYY-MMM] = cur_ym )
RETURN
    CALCULATE (
        [Measure],
        YEAR ( 'Table 2'[Date] ) = cur_year - 1
            && MONTH ( 'Table 2'[Date] ) = _month
    )

Power BI DAX: SAME PERIOD LAST YEAR, PARALELL PERIOD

DATEADD = CALCULATE ( [Total Sales], DATEADD ( 'Calendar'[Date], -1, YEAR ) )
SAMEPERIODLASTYEAR = CALCULATE ( [Total Sales], SAMEPERIODLASTYEAR ( 'Calendar'[Date] ) )

Solved: Dax Function to Calculate same day last year - Microsoft Fabric Community

Sales Yesterday LY =
VAR _Y =
    TODAY () - 1
VAR _YLY =
    DATE ( YEAR ( _Y ) - 1, MONTH ( _Y ), DAY ( _Y ) )
RETURN
    CALCULATE ( SUM ( Sales[salesamount] ), 'Date'[Date] = _YLY )

Best Regards