Bringing week 53 over to the next year as week 1 - CALCULATE(SUM(

[User7664]

Hi 

I have 4 tables, 2 data tables and 2 tables for slicing. The data tables contain a week ID which is the Year+Weeknumber.

What i want to do is create a measure that sums the amount however if the WeekNum is 1 is should take the prior years week 53 and add it to the week 1 number of the current year.

So far i tried:

IF(MAX(WeekNumber[WeekNum])=52,blank(),IF(MAX(WeekNumber[WeekNum])=1,CALCULATE(SUM(DataTable1[Amount]),WeekNumber[WeekNum]=53,YrID[YearNum]-1)))

This however didnt work. It just returns the current years week 53 amount.

THe tables below are joined WeekNum to WeekNum and YearNum to YearNum

Data tables (which include WeekID 202053)

+--------+---------+---------+--------+
| WeekID | WeekNum | YearNum | Amount |
+--------+---------+---------+--------+
| 202001 |       1 |    2020 |   1234 |
| 202001 |       1 |    2020 |   2345 |
| 202104 |       4 |    2021 |   1234 |
+--------+---------+---------+--------+

This table is called WeekNumber  which include weeknum 53

+---------+
| WeekNum |
+---------+
|       1 |
|       4 |
+---------+

This table is called YrID

+---------+
| YearNum |
+---------+
|    2020 |
|    2021 |
+---------+

[Anonymous]

Hi  @User7664  ,

According to your description, I create data from 2020.12.1 to 2021.2.8:

Here are the steps you can follow：

1. Create measure.

Measure =
IF(MAX('Date'[Weeknum])=1,
SUMX(FILTER(ALL('Date'),'Date'[YearNum]=MAX('Date'[YearNum])-1&&'Date'[Weeknum]=53),
'Date'[amount]),MAX('Date'[amount]))

2. Result.

The data of weeknum=1 in 2021 is 21, which is the sum of the amount of weeknum=53 in 2020

You can downloaded PBIX file from here.

Best Regards,

Liu Yang

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

[amitchandak · Accepted solution]

@User7664 , You have to do this in date table as new column

Week = if( WEEKNUM([Date],2)=53,1, WEEKNUM([Date],2))

week year =year(Week End date = 'Date'[Date]+ 7-1*WEEKDAY('Date'[Date],2))