Skip to main content
cancel
Showing results for
Search instead for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

New Member

## Help! Dynamic Year over Year Calculation required

I have a measure called Month Volume, and my goal is to calculate the year-over-year percentage difference in month volume, but with a specific condition.

Typically, this can be accomplished using functions like SamePeriodLastYear, ParallelPeriod, or DateAdd, which I've already employed here:

DAX used:

Month Volume YoY% =
VAR __PREV_YEAR = CALCULATE([Month Volume], PARALLELPERIOD('Date Table'[Date], -12, MONTH), ALL('Date Table'))
RETURN
DIVIDE([Month Volume] - __PREV_YEAR, __PREV_YEAR)

However, the condition I aim to implement differs from the static calculation approach where it compares Jan 2024 with Jan 2023, Feb 2024 with Feb 2023, and so forth. Instead, I need the calculation to dynamically adjust when a date filter is applied. For instance, if the filter starts from Feb 2023, I want it to compare Jan 2024 with Feb 2023, Feb 2024 with March 2023, and continue in this manner as illustrated below:
How can I achieve this?

Notes to consider:

• Month Volume =
VAR _ShipDateDiff = DATEDIFF(MIN('TMR Query'[Ship Date]), MAX('TMR Query'[Ship Date]), DAY) + 1
VAR _RESULT = (more dax)
RETURN _RESULT
1 ACCEPTED SOLUTION
New Member

I understand, and I appreciate your help! It was a requirement from my project, so I had to find a way to implement it. I managed to figure it out myself, and here's how I did it:

Previous Year Month Volume =
VAR _OFFSET  =
VAR MinDateInContext = CALCULATE(MIN('Date Table'[Date]), ALLSELECTED('Date Table'))
RETURN MONTH(MinDateInContext)

VAR _CONDITION = YEAR(CALCULATE(MIN('Date Table'[Date]), DATEADD('Date Table'[Date], -13 + _OFFSET, MONTH)))
VAR _CURRENTYEAR = YEAR(MAX('Date Table'[Date]))
VAR _PREVYEAR = _CURRENTYEAR - 1

VAR RESULT =
IF(_CURRENTYEAR && _CONDITION = _PREVYEAR, CALCULATE([Month Volume], DATEADD('Date Table'[Date], -13 + _OFFSET, MONTH)), BLANK())
RETURN RESULT

YOY% = DIVIDE([Month Volume] - [Previous Year Month Volume], [Previous Year Month Volume])
7 REPLIES 7
Solution Specialist

@georgian4344 - Sorry, I wouldn't encourage this comparison due to seasonality and the number of days in a month. Comparing the 28 days of February with the 31 of May - regardless of year - isn't fair, and that's before we factor in seasonal factors (for example comparing ice cream sales in February and June isnt fair).

I hope you find the answer, but I wont be providing it.

New Member

I understand, and I appreciate your help! It was a requirement from my project, so I had to find a way to implement it. I managed to figure it out myself, and here's how I did it:

Previous Year Month Volume =
VAR _OFFSET  =
VAR MinDateInContext = CALCULATE(MIN('Date Table'[Date]), ALLSELECTED('Date Table'))
RETURN MONTH(MinDateInContext)

VAR _CONDITION = YEAR(CALCULATE(MIN('Date Table'[Date]), DATEADD('Date Table'[Date], -13 + _OFFSET, MONTH)))
VAR _CURRENTYEAR = YEAR(MAX('Date Table'[Date]))
VAR _PREVYEAR = _CURRENTYEAR - 1

VAR RESULT =
IF(_CURRENTYEAR && _CONDITION = _PREVYEAR, CALCULATE([Month Volume], DATEADD('Date Table'[Date], -13 + _OFFSET, MONTH)), BLANK())
RETURN RESULT

YOY% = DIVIDE([Month Volume] - [Previous Year Month Volume], [Previous Year Month Volume])
Solution Specialist

@georgian4344 - I would always advise pushing back on these kinds of requirements, they're setting up false comparisons and anyone working with data should feel empowered to advise that a requirement is going to potentially lead to poor decisions.

Solution Specialist

@georgian4344 - You can try this:

``````Month Volume YoY% =
VAR __PREV_YEAR = CALCULATE([Month Volume], DATEADD('Date Table'[Date], -11, MONTH))

RETURN
DIVIDE([Month Volume] - __PREV_YEAR, __PREV_YEAR)``````

This will compare with a month offset i.e Jan 24 to Feb 23 etc.

If this works, please accept as the solution.

New Member

Thanks for the response Mark,

Unfortunately, this isn't quite the solution I was looking for. While it works for the specific condition where the date starts from Feb 2023, it won't dynamically adapt if the date filter starts from March 2023, April 2023, or any other month. I need a solution that adjusts dynamically based on the starting date of the filter.

Solution Specialist

@georgian4344 - what would you be expecting the calculation to be comparing against if the date filter starts from March 2023 & April 2023?

DATEADD is dynamic, so with the -11, MONTH - March 2023 would compare against April 2022

It's unclear what you are looking for if not this.

New Member

Thanks for your follow up Mark,

I understand that DATEADD with -11 months is dynamic, but it doesn't match the comparison I'm aiming for. If the date filter starts from March 2023, I don't want it to compare March 2023 with April 2022. Instead, I want the comparison to always start with January 2024, then February 2024, and so on.

Here's what I mean:

 Filter Start Date Comparison Calculation Jan 2023 Jan 2024 Jan 2024 vs Jan 2023 Feb 2024 Feb 2024 vs Feb 2023 ... ... March 2023 Jan 2024 Jan 2024 vs March 2023 Feb 2024 Feb 2024 vs April 2023 ... ... April 2023 Jan 2024 Jan 2024 vs April 2023 Feb 2024 Feb 2024 vs May 2023 ... ...

This way, regardless of the starting month of the date filter, the comparisons always begin with January 2024, followed by February 2024, and continue in that sequence.

I hope this clarifies, and again really appreciate your help

## Helpful resources

Announcements

#### Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

#### Fabric Community Update - July 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors