Forum Discussion

SteenSoernesen's avatar
SteenSoernesen
Frequent Visitor
1 year ago
Solved

Compare passenger numbers

Hi    I'm looking a DAX formula that allows me to compare a quarter from 2 different years, so I can show the increase or decrease in %   Any ideas to a useful DAX formula?
  • v-veshwara-msft's avatar
    v-veshwara-msft
    1 year ago

    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.

anilelmastasi's avatar
anilelmastasi
Icon for Super User rankSuper User
1 year ago

If you want to calculate it manually, you can simply use:

Sales CY Quarter =
CALCULATE(
[Total Sales],
FILTER(
ALL('Date'),
'Date'[Year] = MAX('Date'[Year]) &&
'Date'[Quarter] = MAX('Date'[Quarter])
)
)

 

But I suggest you to this dynmically if possible:

 

Create a “Years Ago” parameter
Go to Modeling → New Parameter → Numeric Range.

Set:

Name → Years Ago

Minimum → 1

Maximum → 5 (or whatever range you want)

Increment → 1

This will create a table called Years Ago and a slicer that users can control on the report.

 

Create the dynamic comparison measure

Sales N Years Ago Quarter =
VAR SelectedYearsAgo = SELECTEDVALUE('Years Ago'[Years Ago])
VAR CurrentYear = MAX('Date'[Year])
VAR CurrentQuarter = MAX('Date'[Quarter])
RETURN
CALCULATE(
[Total Sales],
FILTER(
ALL('Date'),
'Date'[Year] = CurrentYear - SelectedYearsAgo &&
'Date'[Quarter] = CurrentQuarter
)
)

 

Create the % change measure

% Change vs N Years Ago =
DIVIDE(
[Total Sales] - [Sales N Years Ago Quarter],
[Sales N Years Ago Quarter],
0
)

If this solved your issue, please mark it as the accepted solution. ✅