Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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
Solved! Go to 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.
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
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.
User | Count |
---|---|
98 | |
90 | |
82 | |
70 | |
67 |
User | Count |
---|---|
115 | |
104 | |
101 | |
72 | |
64 |