Forum Discussion
13 weeks rolling with customer
- 3 years ago
In general, if you cannot share your pbix, creating a sample report reproducing your issue will give more precise answers: How to Get Your Question Answered Quickly
Since you have not shared anything, I will assume your model only contains a single table, 'Orders'. I would at a separate date table, with relationship to order.
Then I would create this column in the date table:WeekYearNum = var _startDate=DATE(2016,1,1) var _currentDate=CALCULATE(SELECTEDVALUE(Dates[Date])) var _distinctWeekNumbers = CALCULATETABLE(VALUES(Dates[Year-Week]),Dates[Date]<=_currentDate) return COUNTROWS(_distinctWeekNumbers)This is a running week-year-number, so you can easily move back/forward any number of weeks, without having to think about changing years.
With this column you can write your measure like this:Sales last 13 weeks = VAR _currentWeek = CALCULATE ( SELECTEDVALUE ( Dates[WeekYearNum] ) ) RETURN CALCULATE ( SUM ( 'Table'[SalesCol] ), FILTER ( ALL ( Dates ), Dates[WeekYearNum] > _currentWeek - 13 && Dates[WeekYearNum] <= _currentWeek ) )
Then drag the Dates[Date] into the filter for your visual, choose relative date filtering, and choose show last 13 weeks:
In general, if you cannot share your pbix, creating a sample report reproducing your issue will give more precise answers: How to Get Your Question Answered Quickly
Since you have not shared anything, I will assume your model only contains a single table, 'Orders'. I would at a separate date table, with relationship to order.
Then I would create this column in the date table:
WeekYearNum =
var _startDate=DATE(2016,1,1)
var _currentDate=CALCULATE(SELECTEDVALUE(Dates[Date]))
var _distinctWeekNumbers = CALCULATETABLE(VALUES(Dates[Year-Week]),Dates[Date]<=_currentDate)
return
COUNTROWS(_distinctWeekNumbers)
This is a running week-year-number, so you can easily move back/forward any number of weeks, without having to think about changing years.
With this column you can write your measure like this:
Sales last 13 weeks =
VAR _currentWeek =
CALCULATE ( SELECTEDVALUE ( Dates[WeekYearNum] ) )
RETURN
CALCULATE (
SUM ( 'Table'[SalesCol] ),
FILTER (
ALL ( Dates ),
Dates[WeekYearNum] > _currentWeek - 13
&& Dates[WeekYearNum] <= _currentWeek
)
)
Then drag the Dates[Date] into the filter for your visual, choose relative date filtering, and choose show last 13 weeks: