Create column Date N-1 based on week ISO

[Fardy]

Hello everyone,

I'm creating a date table with a 'Date' column and I want to add a 'Date N-1' column corresponding to the same day (Monday, Tuesday, Wednesday...) of the same week of the previous year.

For example, if I take Wednesday May 1, 2024, I want Wednesday May 3, 2023.

I work with week in ISO format.

Thank you

[v-zhouwen-msft]

Hi @Fardy ,

Please follow these steps:
1. Use the following DAX expression to create a column

Date N-1 = 
VAR _a = WEEKNUM([Date],2)
VAR _b = WEEKDAY([Date],2)
VAR _c = DATE(YEAR([Date])-1,1,1) + (_a-1) * 7 + _b
RETURN _c

2.Final output

[Fardy · Accepted solution]

Hi @v-zhouwen-msft, 

Thanks for your reply, but this formula won't work in 2025, as my screenshot shows.

I've found an alternative solution which consists in creating my date table with these two columns in a csv file instead of generating it with M or DAX.