Forum Discussion

NDDD's avatar
NDDD
Icon for Helper I rankHelper I
3 years ago
Solved

13 weeks rolling with customer

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...
Need Help
  • sturlaws's avatar
    sturlaws
    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:

     

     

NDDD's avatar
NDDD
Icon for Helper I rankHelper I
3 years ago

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's avatar
sturlaws
Icon for Resident Rockstar rankResident Rockstar
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:

 

 

  • NDDD's avatar
    NDDD
    Icon for Helper I rankHelper I
    3 years ago

    sturlaws many thanks!