Forum Discussion
How do I calculate last 8 weeks using a WeekEND date column?
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
)
This is excellent - thank you so much for this. How do you begin to think through the logic? I struggle so much with this (framing the solution in my head)
Glad to hear that works. The concept of Relative columns in DimDate is the basis of the logic. Once the Relative columns are created in DimDate, measures can be easily created. A robust date table is essential to any data model.
Do you know if convert and integer is able to be used in Power Pivot? It's not working in Power Pivot for whatever reason.
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?
