Forum Discussion

Fardy's avatar
Fardy
Frequent Visitor
2 years ago
Solved

Create column Date N-1 based on week ISO

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

Need Help
  • Fardy's avatar
    Fardy
    2 years ago

    Hi Anonymous, 

     

    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.

2 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    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's avatar
      Fardy
      Frequent Visitor
      2 years ago

      Hi Anonymous, 

       

      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.