13 weeks rolling with customer

[NDDD]

Hi, 

 I created  measure that is suming 13 weeks prior current week and it is working fine when used "year week" column. Now when I try to add "customer name" from same table "year week" 13 weeks value is same for all customers, meainning it is not seprating the 13 weeks value by customer.

Any solution? 

Txn 

[NDDD]

Thank you @sturlaws  for fast replay, 

 I can't share the file sadly

 Bellow I managed to get 13 week order received and 13 week sales to get book to bill

I used measure that is working fine when adding "year -week" as above 

13 WEEKS OR = VAR CURRENTWEEK = MAX(ORDERS[DATE.WEEKSUM])

RETURN

CALCULATE(SUM(ORDERS [VALUE]),FILTER(ALL(ORDERS), ORDERS [DATE.WEEKSUM]>CURRENTWEEK - 13 && ORDERS [DATE.WEEKSUM]<=CURRENTWEEK))

Now when I add Customer I get wrong result, point is to have "year-week" per customer to show 13 weeks or received

I hope I'm clear and thank you in advance 

[sturlaws · Accepted solution]

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:

[NDDD]

@sturlaws many thanks! 

[sturlaws]

@NDDD,

could you share your pbix-file? Or if it contains data you cannot share, create sample report reproducing your issue?

Cheers,
Sturla

How to Get Your Question Answered Quickly