I have a daily date table and I would like one of the columns in there to be the date of the previous tuesday. By this, I mean that I would a column that contains the same date for a Wed, Thur, Fri, Sat, Sun, Mon & Tue before then updating 7 days in advance to the date of the Wednesday. Essentially having a week running from Wednesday to Tuesday.
To give you an example:
Date | Weekday | Week Starting Wednesday |
22-Dec-2020 | Monday | 16-Dec-2020 |
22-Dec-2020 | Tuesday | 16-Dec-2020 |
23-Dec-2020 | Wednesday | 23-Dec-2020 |
24-Dec-2020 | Thursday | 23-Dec-2020 |
25-Dec-2020 | Friday | 23-Dec-2020 |
26-Dec-2020 | Saturday | 23-Dec-2020 |
27-Dec-2020 | Sunday | 23-Dec-2020 |
28-Dec-2020 | Monday | 23-Dec-2020 |
29-Dec-2020 | Tuesday | 23-Dec-2020 |
30-Dec-2020 | Wednesday | 30-Dec-2020 |
31-Dec-2020 | Thursday | 30-Dec-2020 |
1-Jan-2021 | Friday | 30-Dec-2020 |
I have found a couple of different methods on line but neither work 100% with them either being a day out through the year, or the calculation being made using the previous year end, which means it stops working when it hits a new year.
Any suggestions on how to do this?
Here is my full code to create the table:
Date =
VAR MinYear = YEAR ( MIN ( 'Real_Time_ Data'[Createddate] ) )
VAR MaxYear = YEAR ( MAX ( 'Real_Time_ Data'[Createddate] ) )
RETURN
ADDCOLUMNS (
FILTER (
CALENDARAUTO( ),
AND ( YEAR ( [Date] ) >= MinYear, YEAR ( [Date] ) <= MaxYear )
),
"Weekday", FORMAT ( [Date], "dddd" ),
"Week Start Mon", format([Date] - weekday([Date],2) + 1,"dd-mmm-yyyy"),
"Week Num Monday", Year([Date]) & Weeknum( [Date], 2 ),
"Month Name", FORMAT ( [Date], "mmmm" ),
"Monthly", FORMAT([Date],"MMM") & " " & YEAR([Date]),
"Calendar Year", format ( [Date] , "yyyy" ),
"Quarter", "Q" & TRUNC ( ( MONTH ( [Date] ) - 1 ) / 3 ) + 1 & " " & YEAR ( [Date] )
)
