Week over Week Change - CROSS YEARs

[h_l]

Hi there, 

When I apply following DAX to get week over week change and apply in visual, it's found that, there is calculation error if a WeekNumber exists in more than one Year.

Measure =

VAR lastWeekAmount =

CALCULATE(

SUM(General_Performance[Impressions]),

FILTER(

ALL('Date'),

'Date'[intWeekNum]

= MIN('Date'[intWeekNum])-1

)

)

Return

DIVIDE(SUM(General_Performance[Impressions])-lastWeekAmount,lastWeekAmount,0)

Data from 2020-1-1 to 2021-2-20 as following:

Date:

As seen, Week 1 to Week 6 existing in both year 2020 and 2021. 

Then the calculated change+/- in those weeks are not correct.

Calculated change in visual:

Actual Change in Year 2020 Wk3:

But if a week number exists in 2020 only, there is no error:

Calculated change in 2020 Wk26

Actual change:

Is there a way to resolve this and to get a  "cross-year-consecutive week over week" change?

Thank you.

H

[v-alq-msft · Accepted solution]

Hi, @h_l 

Based on your description, I created data to reproduce your scenario. The pbix file is attached in the end.

Table:

Date(a calculated table):

Date = 
ADDCOLUMNS(
    CALENDARAUTO(),
    "YearWeek",
    YEAR([Date])*100+WEEKNUM([Date])
)

There is a relationship between two tables. You may create a measure as below.

Result = 
var lastweeknum = 
CALCULATE(
    MAX('Date'[YearWeek]),
    FILTER(
        ALL('Date'),
        [YearWeek]<MAX('Date'[YearWeek])
    )
)
var lastweekamount = 
CALCULATE(
    SUM('Table'[Impressions]),
    FILTER(
          ALL('Date'),
          [YearWeek]=lastweeknum
    )
)
return
DIVIDE(
    SUM('Table'[Impressions])-lastweekamount,
    lastweekamount,
    0
)

Result:

Best Regards

Allan

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[h_l]

Thanks a lot for the help! @v-alq-msft 

[amitchandak]

@h_l , Refer my WOW on week blog, usinf a seperate date table and week rank you can work on week across years

Power BI — Week on Week and WTD
https://medium.com/@amitchandak.1978/power-bi-wtd-questions-time-intelligence-4-5-98c30fab69d3
https://community.powerbi.com/t5/Community-Blog/Week-Is-Not-So-Weak-WTD-Last-WTD-and-This-Week-vs-La...
https://www.youtube.com/watch?v=pnAesWxYgJ8

if needed refer

2.Any Weekday Week - Start From Any day of Week
https://community.powerbi.com/t5/Community-Blog/Any-Weekday-Week-Decoding-Date-and-Calendar-2-5-Powe...

[h_l]

Hi @amitchandak , thanks for the reply with the useful content, i've added into bookmark.

However, after creating Last Week by following the content, it seems the problem is still there becase the "Year" is not considered in the visual calculation in OP.

The issue is, e.g. WK2 is exsting in both 2020 and 2021, two years, so, in the visual, it calculates:

2020 Wk2 + 2021 WK2 as a total then compare to 2020 Wk1 + 2021 Wk1.

Hope there is a way to resolve it.

Have a good day.

H