How to fix the Week End Date when it shows up twice (Week 53/Week 1)?

[Anonymous]

Hello -

I have a weekly table that measures the total by the end of each week.

However, I am encountering a problem where WeekEndDate (formula below)

WeekEndDate = 'Calendar'[Date]+7-WEEKDAY('Calendar'[Date],1)

Year&Week	WeekEndDate	Actions
2021-2	9 January, 2021	15,202
2021-1	2 January, 2021	18,020
2020-53	2 January, 2021	19,101
2020-53	26 December, 2020	29,101

The formula for Year&Week:

Text.From([Year]) & "-" & Text.From([Week of Year])

And Week of Year:

Date.WeekOfYear([Date])

What can I do to fix this?

Thanks!

[v-yangliu-msft · Accepted solution]

Hi  @Anonymous  ,
I used the calendar function to create data from December 1, 2020 to February 2, 2021

Table = CALENDAR(DATE(2020,12,1),DATE(2021,2,1))

Here are the steps you can follow：

1. Create measure

Show weeks:

Year&Week = YEAR('Table'[Date])&"-"&WEEKNUM('Table'[Date],1)

Show last day of week:

WeekEndDate = 'Table'[Date]+7 - WEEKDAY('Table'[Date],1)

2. Result.

See yellow mark for corresponding information

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.

[parry2k]

@Anonymous not fully clear what you are looking for? What is the issue? what is the expected output? Read this post to get your answer quickly.

https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490