Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

Week over Week Change - CROSS YEARs

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

Need Help
Show and Tell
  • v-alq-msft's avatar
    v-alq-msft
    5 years ago

    Hi, Anonymous 

     

    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.

4 Replies