Forum Discussion
Solved
Week 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 ...
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 )
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!
Thank you very much!
Is there anything that I can change in the formula that would have the week number be positive?