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 value is same for all customers, meainning it is not seprating the 13 weeks value by customer.

Any solution? 

 

Txn 

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:

     

     

4 Replies

  • 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: