Compare passenger numbers

Hi SteenSoernesen ,
Thanks for your follow-up. Since your dataset contains only Year, Quarter, and Passenger Count (without a full date column), you can still achieve a year-over-year quarter comparison by adding a calculated column to extract the numeric quarter and using measures to calculate the % change.Here's a step by step approach:

Assuming the data table name is PassengerData
1. Create a numeric quarter column to convert "1. Kvartal" to 1, and so on:

QuarterNumber =

SWITCH(

    TRUE(),

    SEARCH("1", 'PassengerData'[Kvartal], 1, 0) > 0, 1,

    SEARCH("2", 'PassengerData'[Kvartal], 1, 0) > 0, 2,

    SEARCH("3", 'PassengerData'[Kvartal], 1, 0) > 0, 3,

    SEARCH("4", 'PassengerData'[Kvartal], 1, 0) > 0, 4

)

 

 

2. Measure to get total passengers:

Total Passengers = SUM('PassengerData'[Passagertal])

 

3. Measure to get passengers from the same quarter last year:

Passengers Last Year Same Quarter =

VAR CurrentYear = MAX('PassengerData'[År])

VAR CurrentQuarter = MAX('PassengerData'[QuarterNumber])

RETURN

CALCULATE(

    [Total Passengers],

    FILTER(

        ALL('PassengerData'),

        'PassengerData'[År] = CurrentYear - 1 &&

        'PassengerData'[QuarterNumber] = CurrentQuarter

    )

)

 

4. Measure to calculate % change:

% Change vs Last Year Same Quarter =

DIVIDE(

    [Total Passengers] - [Passengers Last Year Same Quarter],

    [Passengers Last Year Same Quarter],

    0

)

 

Once these are in place, you can use a Matrix visual with Year and Quarter to show the results as shown below:

Also, to Format % Change vs Last Year Same Quarter as percentage: Go to Model view --> select the measure --> Format as Percentage with 1 decimal place.

 

You can also apply conditional formatting to the % change measure to highlight increases and decreases.

 

Hope this helps. Please reach out for further assistance.
If this post helps, then please consider to give a kudos and Accept as the solution to help the other members find it more quickly.

Thank you.