Compare passenger numbers

[SteenSoernesen]

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?

[SteenSoernesen]

Looking at the incoming data. I don't have a date Column I can use.

I only have a quarter and year Columns

[v-veshwara-msft · Accepted solution]

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.

[v-veshwara-msft]

Hi @SteenSoernesen ,

Following up to see if your query has been resolved. If any of the responses helped, please consider marking the relevant reply as the 'Accepted Solution' to assist others with similar questions.

If you're still facing issues, feel free to reach out.

Thank you.

[v-veshwara-msft]

Hi @SteenSoernesen ,
Thanks for using Microsoft Fabric Community.

Just checking in to see if you query is resolved and if any responses were helpful. If so, kindly consider marking the helpful reply as 'Accepted Solution' to help others with similar queries. 

Otherwise, feel free to reach out for further assistance.

Thank you.

Also thanks to @sjoerdvn and @anilelmastasi for your prompt responses.

[sjoerdvn]

Assuming you have a measure [Sales], a date table 'Calendar' with a date column 'date' and a column that has the quarter info. If you then filter on one year and quarter, you can use the measure below to get the change from the same quarter the previous year.

change from same quarter previous year = 
VAR cq = [Sales]
VAR pq = CALCULATE([Sales], PARALLELPERIOD(Calendar[Date],-4,quarter))
RETURN DIVIDE(cq - pq,pq,0)

[anilelmastasi]

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. ✅