Forum Discussion

NDDD's avatar
NDDD
Helper 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...
  • 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: