Dynamic Calendar: Incorrect Week Number (53 and 1)

[Anonymous]

Hello -

I currently have a visual that compares the amount of Impressions from a Week over Week (WoW) basis.

The formula works fine, however, I noticed that when it comes to the end of the year, and the start of the new year, the calculation malfunctions. And the reason is that the Week of Year has two different values for the WeekDate.

Example below:

On Sunday, December 27, 2020, the Week of Year contains 53 and 1.

Is there anyway to fix this?

Here is the current PowerQuery formula I have for WeekOfYear:

= Table.AddColumn(#"Inserted Start of Week (WeekDate)", "Week of Year", each Date.WeekOfYear([Date]), Int64.Type)

Calculation used for Total Impressions Last Week:

TOTAL_IMPRESSIONS_LAST_WEEK = VAR CURRENTWEEK = SELECTEDVALUE('Calendar'[Week of Year])

VAR CURRENTYEAR = SELECTEDVALUE('Calendar'[Year])

VAR MAXWEEKNUMBER = CALCULATE(MAX('Calendar'[Week of Year]), ALL('Calendar'))

RETURN

SUMX(

      FILTER(ALL('Calendar'),

             IF(CURRENTWEEK = 1,

                  'Calendar'[Week of Year] = MAXWEEKNUMBER && 'Calendar'[Year] = CURRENTYEAR - 1,

                  'Calendar'[Week of Year] = CURRENTWEEK - 1 && 'Calendar'[Year] = CURRENTYEAR)),

       [TOTAL_IMPRESSIONS_THIS_WEEK])

[AlB]

@Anonymous 

Check this out as well

https://community.powerbi.com/t5/Desktop/how-to-adjust-week-number-in-date-dale/m-p/1598957#M647578

Please mark the question solved when done and consider giving a thumbs up if posts are helpful.

Contact me privately for support with any larger-scale BI needs, tutoring, etc.

Cheers 

[AlB]

Hi @Anonymous 

See if this can help

https://community.powerbi.com/t5/Power-Query/Calender-table-with-Date-WeekOfYear-week-53/m-p/1597115

Please mark the question solved when done and consider giving a thumbs up if posts are helpful.

Contact me privately for support with any larger-scale BI needs, tutoring, etc.

Cheers 

[Anonymous]

Hi @AlB 

So, I added the ISO Week Number column to my Calendar table, and it seems to be doing the trick. But now, it doesn't work in my WoW visual.

The Previous Week Calculation I am using below is returning blank:

TOTAL_IMPRESSIONS_LAST_WEEK = VAR CURRENTWEEK = SELECTEDVALUE('Calendar'[ISOWeekNum])

VAR CURRENTYEAR = SELECTEDVALUE('Calendar'[Year])

VAR MAXWEEKNUMBER = CALCULATE(MAX('Calendar'[ISOWeekNum]), ALL('Calendar'))

RETURN

SUMX(

FILTER(ALL('Calendar'),

IF(CURRENTWEEK = 1,

'Calendar'[ISOWeekNum] = MAXWEEKNUMBER && 'Calendar'[Year] = CURRENTYEAR - 1,

'Calendar'[ISOWeekNum] = CURRENTWEEK - 1 && 'Calendar'[Year] = CURRENTYEAR)),

[TOTAL_IMPRESSIONS_THIS_WEEK])

Any idea what I'm missing?