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

Reply
georgian4344
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:

month volume YOY%.PNG

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:
new calculation help.PNG
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
  • relationship.PNG
1 ACCEPTED SOLUTION

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])

View solution in original post

7 REPLIES 7
mark_endicott
Solution Specialist
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.   

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])

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

mark_endicott
Solution Specialist
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. 

 

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.

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

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 DateComparisonCalculation
Jan 2023Jan 2024Jan 2024 vs Jan 2023
 Feb 2024Feb 2024 vs Feb 2023
 ......
March 2023Jan 2024Jan 2024 vs March 2023
 Feb 2024Feb 2024 vs April 2023
 ......
April 2023Jan 2024

Jan 2024 vs April 2023

 Feb 2024Feb 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
July 2024 Power BI Update

Power BI Monthly Update - July 2024

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

July Newsletter

Fabric Community Update - July 2024

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