Forum Discussion
Calculating Last Week Last Year
Hi all,
I am trying to create a measure that returns the week end date last year. The week end date is always need to be on a Saturday, which causes some problems when trying to account for the one day offset so doing a dateadd with -364 days doesn't work in the long term. For example:
Week number 1 in 2020 - 04/01/2020
Week number 1 in 2021 - 02/01/2020
Week number 1 in 2022 - 01/01/2020
Week number 1 in 2023 - 07/01/2020
The date table I am using has a "Date Key" and a "Week End Date" on the same row, as well as a week number - see below:
I have tried using IF(HASONEVALUE('DimensionDate'[Calendar Year]),CALCULATE(VALUES('DimensionDate'[Week End Date]),FILTER(ALL('DimensionDate'),AND('DimensionDate'[Calendar Year]=VALUES('DimensionDate'[Calendar Year])-1,CONTAINS(VALUES('DimensionDate'[Calendar Week Number]),'DimensionDate'[Calendar Week Number],'DimensionDate'[Calendar Week Number]))))))
But this does not work for the start weeks of the year, as some "Week End Dates" technically have two Calendar Years.
Apologies if I haven't explained this very well, but any help on calculating "Week End Date" based on Saturdays would be a great help!
Hi,
I managed to figure it out - maybe I wasn't so clear on the explanation but the end result might help! Here is the measure I ended up with:
LOOKUPVALUE('DimensionDate'[Week End Date],'DimensionDate'[Year & Cal WeekNum],VALUES('DimensionDate'[Year & Cal WeekNum])-100)
This returns the week end date for last year - just need to factor in years with 53 weeks and then I am all set.
Thanks for the advice though!
5 Replies
I'm not sure I am 100% following this but I have a ton of date/week manipulations in the Quick Measures gallery that you can check out. Can you provide some sample data in text and your expected output? Please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490
Hi Anonymous ,
I am not too clear about requirements. Can you create an excel sheet of dates with the result you are expecting and paste it here.
As requested by you to calculate the weekend date based on Saturday you can use the calculated column as
Column = [Date] - MOD([Date]-1, 7) + 6The date here refers to the date column of your date table.CheersCheenuSingHi,
Thank you for your quick responses and apologies for not explaining it very well. It is my first post here so thanks for explaining how to get posts answered Greg_Deckler
I have uploaded a sample file to dropbox (https://www.dropbox.com/s/1l2xpcc1z2lje5o/Last%20Week%20Last%20Year%20Example.xlsx?dl=0)
Column E contains the expected result, but ideally it would be good to have it as a measure rather than a calculated column.
I hope that helps and thanks again!
Hi,
I managed to figure it out - maybe I wasn't so clear on the explanation but the end result might help! Here is the measure I ended up with:
LOOKUPVALUE('DimensionDate'[Week End Date],'DimensionDate'[Year & Cal WeekNum],VALUES('DimensionDate'[Year & Cal WeekNum])-100)
This returns the week end date for last year - just need to factor in years with 53 weeks and then I am all set.
Thanks for the advice though!
Anonymous
Seems like already solved it. Typically same week day is 364 days behind. As you already have formula to find week end date you can use that on date that is 364 days behind.
Can you mark you reply as solution, so that other can take hekp
