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?

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