Line Graph by Week issues moving from 2020 to 2021

Hi caebrida ,

 

You can probably create a calculated concatenated column to fecth year+week number. That should suffice your requirement. This is the easiest way.

DAX for New Column:

Week No = YEAR(TableName[Date_Column]) & " W" & WEEKNUM(TableName[Date_Column],1)

 

2nd method would be to create indexing so that 2021's Week1 comes after 2020's week53. This method would be a bit lengthy.

 

 

 

Hi caebrida 

I think your problem is that the weeknum will start by 1 again in a new year instead of continuous on the basis of 2020.

You can build a continuous weeknum by calculated column.

My Sample:

Date = 
ADDCOLUMNS (
    CALENDAR ( DATE ( 2020, 12, 01 ), DATE ( 2021, 02, 01 ) ),
    "Year", YEAR ( [Date] ),
    "WeekNum", WEEKNUM ( [Date], 2 )
)

Calculated column:

Accumulate_Week_Num = 
IF (
    RANKX ( 'Date', 'Date'[YEAR],, ASC, DENSE ) <> 1,
    SUMX (
        SUMMARIZE (
            FILTER ( 'Date', 'Date'[YEAR] < EARLIER ( 'Date'[YEAR] ) ),
            'Date'[Year],
            "Maxweeknum-1",
                MAXX (
                    FILTER ( 'Date', 'Date'[YEAR] = EARLIER ( 'Date'[YEAR] )),
                    'Date'[WeekNum]
                ) - 1
        ),
        [Maxweeknum-1]
    ) + 'Date'[WeekNum],
    WEEKNUM ( 'Date'[Date], 2 )
)

Result is as below.

Best Regards,

Rico Zhou

 

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