Forum Discussion
Nickster424
Advocate I
Advocate IWeek Number beginning with latest date in my data set
I would like to create a week number column that starts with the latest date in my data table.
For example "sales date week ending" is 4/4/2026 and I would like this to be week 1 in the week number column.
Then I would like to calculate latest 13 weeks and latest 4 weeks using this week number column.
All help is appreciated!
Please try the calculated columns below:
Week Number = VAR _LatestDate = CALCULATE ( MAX ( 'Sales'[Sales Date Week Ending] ), ALL ( 'Sales' ) ) RETURN INT ( DIVIDE ( _LatestDate - 'Sales'[Sales Date Week Ending], 7 ) ) + 1 Sales Last 13 Weeks = CALCULATE ( [Total Sales], 'Sales'[Week Number] <= 13 ) Sales Last 4 Weeks = CALCULATE ( [Total Sales], 'Sales'[Week Number] <= 4 )
4 Replies
- parry2k
Super User
here you go
Week Offset = VAR __MaxWeekDate = CALCULATE ( MAX ( 'Dim Date'[Date] ), ALL ( ) ) RETURN DATEDIFF ( 'Dim Date'[Date], __MaxWeekDate, WEEK ) - cengizhanarslan
Please try the calculated columns below:
Week Number = VAR _LatestDate = CALCULATE ( MAX ( 'Sales'[Sales Date Week Ending] ), ALL ( 'Sales' ) ) RETURN INT ( DIVIDE ( _LatestDate - 'Sales'[Sales Date Week Ending], 7 ) ) + 1 Sales Last 13 Weeks = CALCULATE ( [Total Sales], 'Sales'[Week Number] <= 13 ) Sales Last 4 Weeks = CALCULATE ( [Total Sales], 'Sales'[Week Number] <= 4 ) - parry2k
Nickster424 add a new column as week offset in your date table
Week Offset = VAR __MaxWeekDate = CALCULATE ( MAX ( 'Dim Date'[Date] ), ALL ( ) ) RETURN DATEDIFF ( __MaxWeekDate, 'Dim Date'[Date], WEEK )change the formula as you see fit. Cheers!
- Nickster424
Thank you very much!
Is there anything that I can change in the formula that would have the week number be positive?
