YoY Comparison, same week number but different month bucket

[cj_oat28]

Hi all,

I would like to seek your support, as table below, week 27 in TY vs LY, they are actually the same week number but belong to different month where LY WK27 belongs to May while TY WK27 belongs to June

Basically if I removed out "Month" & "Month Name", there will be no issue to compare YoY for WK27, but since I still want to keep "Month" then the result is showing %YoY as 100%.

Is there any way I can compare these 2 number highlighted in yellow while I can still keep Month in header level?

I dont expect to make them show in the same column since they belong to different month but just expecting the result of YoY to show 8% instead of 100%

This is the Fomula I'm using

The leveel of data is only at month and week number, I do not have data by date.

__%YoY Value = DIVIDE([__Sales Value (TY)],[__Sales Value (LY)])-1)

[v-agajavelly]

Hi @cj_oat28 ,

I hope the response provided helped in resolving the issue. If you still have any questions, please let us know we are happy to address.

Regards,
Akhil.

[v-agajavelly]

Hi @cj_oat28 ,

There, just wanted to follow up were you able to test out the suggestion of removing the month context in the measure for your Week TY vs LY comparison? Curious to hear if it worked as expected, and happy to help fine-tune if you’re still running into issues.

Regards,
Akhil.

[v-agajavelly]

Hi @cj_oat28 ,

Just checking in to see if you had a chance to try out any of the solutions shared by super users. Happy to know if removing the month context within the measure helped achieve the Week TY vs LY comparison you're aiming for, while still keeping the month header in your matrix. Let us know how it worked out, and feel free to share a snippet or describe any remaining issues  happy to help troubleshoot further if needed.

Regards,
Akhil.

[v-agajavelly]

Hi @cj_oat28,

A big thank you to all three Super Users @Sandip_Palit , @rohit1991 and @danextian for your insightful solutions and clear breakdowns. Each of your approaches handles the YoY comparison across different month buckets in an effective way, especially when weeks shift between months across years.
@cj_oat28 considering the challenge is mainly due to context filtering at the month level, is there any chance you could try applying the measures shared by the Super Users and see if that resolves the issue

You don’t need to remove the month column from the matrix. These solutions allow you to retain the Month header while still comparing Week TY vs Week LY accurately by removing or ignoring the month context only within the measure logic  exactly what you're aiming for.

Would love to hear if any of this work in your actual dataset or if further helps are needed.

Best regards,
Akhil G.

[danextian]

Hi @cj_oat28 

Assuming you are using a separate dates dimension table which you should, try the following:

Week sales for the year =
VAR _CY =
    CALCULATE (
        [Total Sales],
        FILTER (
            ALL ( 'Date' ),
            'Date'[Week of Year] = MAX ( 'Date'[Week of Year] )
                && 'Date'[Calendar Year] = MAX ( 'Date'[Calendar Year] )
        )
    )
VAR _LY =
    CALCULATE (
        [Total Sales],
        FILTER (
            ALL ( 'Date' ),
            'Date'[Week of Year] = MAX ( 'Date'[Week of Year] )
                && 'Date'[Calendar Year]
                    = MAX ( 'Date'[Calendar Year] ) - 1
        )
    )
RETURN
    _CY - _LY

Please note that the result will appear in both months a week number exists in.

[rohit1991 · Accepted solution]

Hi @cj_oat28 

This issue happens because your YoY calculation is happening within the Month context, so if Week 27 falls in different months for TY and LY, Power BI compares Week 27 of TY (June) to Week 27 of LY (May), but only when both exist in the same month bucket. If not, LY value is blank and you get -100% or 100%.

You need to ignore the Month context for your YoY calculation and compare based on Year and Week Number only. Create your YoY measure like this:

%YoY Value =
VAR CurrentWeek = SELECTEDVALUE('Calendar'[Week Number])
VAR Sales_TY = CALCULATE([__Sales Value (TY)], ALL('Calendar'[Month], 'Calendar'[Month Name]))
VAR Sales_LY = CALCULATE([__Sales Value (LY)], ALL('Calendar'[Month], 'Calendar'[Month Name]))
RETURN
DIVIDE(Sales_TY, Sales_LY) - 1

This measure removes the Month/Month Name context so Week 27 TY and Week 27 LY will always be compared, regardless of which month they are bucketed into.

The YoY % is correct for each week, even if the weeks fall into different months. You can still display Month/Month Name in your matrix, but this measure will always calculate YoY at the week level across years, ignoring the month split.

[Oat]

Thank you! I tried and it works for me, I get the result as expected.

[Sandip_Palit]

1. Create the New Measure
In Power BI, create a new measure and paste the following DAX code. Be sure to replace 'YourTable' with the actual name of your date or data table containing the Month, Month Name, and Week Number columns.

%YoY Value (Corrected) =
VAR CurrentWeek =
SELECTEDVALUE ( 'YourTable'[Week Number] )
VAR SalesTY =
[__Sales Value (TY)]
VAR SalesLY =
CALCULATE (
[__Sales Value (LY)],
REMOVEFILTERS ( 'YourTable'[Month], 'YourTable'[Month Name] ),
'YourTable'[Week Number] = CurrentWeek
)
RETURN
IF (
NOT ISBLANK ( SalesTY ) && NOT ISBLANK ( SalesLY ),
DIVIDE ( SalesTY, SalesLY ) - 1
)
2. Add the New Measure to Your Visual
Replace the old __%YoY Value measure in your matrix with this new %YoY Value (Corrected) measure. The result should now calculate correctly.

This will give you the desired 8% result for Week 27 while allowing you to keep the Month in your column headers.

If this explanation and solution resolve your issue, please like and accept the solution.