Forum Discussion
Solved
How do I calculate last 8 weeks using a WeekEND date column?
Hey all - kind of loaded question here. It's been a while since I've had to use any of this here. So I have a dataset fact table with a date column (1 day per row), and another column that shows ...
Try this solution.
1. Create calculated column in date table:
Relative Week = VAR vToday = TODAY() VAR vWeekEndDate = [Date] - WEEKDAY ( [Date], 1 ) + 7 VAR vWeekEndDateToday = vToday - WEEKDAY ( [Date], 1 ) + 7 VAR vResult = CONVERT ( ( vWeekEndDate - vWeekEndDateToday ) / 7, INTEGER ) RETURN vResult2. Create measure:
Rolling 8 Weeks = CALCULATE ( SUM ( FactTable[Amount] ), DimDate[Relative Week] >= -8, DimDate[Relative Week] < 0 )
Try this solution.
1. Create calculated column in date table:
Relative Week =
VAR vToday = TODAY()
VAR vWeekEndDate =
[Date] - WEEKDAY ( [Date], 1 ) + 7
VAR vWeekEndDateToday =
vToday - WEEKDAY ( [Date], 1 ) + 7
VAR vResult =
CONVERT ( ( vWeekEndDate - vWeekEndDateToday ) / 7, INTEGER )
RETURN
vResult
2. Create measure:
Rolling 8 Weeks =
CALCULATE (
SUM ( FactTable[Amount] ),
DimDate[Relative Week] >= -8,
DimDate[Relative Week] < 0
)
I'm trying to compare the 8 weeks that preview those to see progression or regression of sales, is there a way I can create a column of comparison?