Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago
Solved

How to get rolling Wow values from the tall format data set

Item Week Driver Score Resp 1 551 D0 6 98 1 551 D1 6.87 189 1 551 D2 6.67 63 1 551 D3 5.3 70 1 552 D0 6.92 182 1 552 D1 7.24 133 1 552 D2 4.57 ...
  • Cmcmahan's avatar
    Cmcmahan
    7 years ago

    Ahh, now THIS makes sense.  So assuming you have a measure for the current timeframe [Weighted Mean] like this:

    Weighted Mean = SUMX('Table', [Score] * [Resp]) / SUM('Table'[Resp])

    The basic form of the DAX for a single week's WoW value would be:

    WoW Mean = [Weighted Mean] - CALCULATE( [Weighted Mean], FILTER( ALL('Table'[Week]), 'Table'[Week] = SELECTEDVALUE('Table'[Week])-1))

    Since you just want the difference between the current [Weighted Mean] and the [Weighted Mean] with a different set of weeks, you can do that with CALCULATE syntax to change the context for part of the measure. 

     

    However, you've gone a step further and added the requirement that if there are multiple weeks available via filter/slicer, then it should use the same number of previous weeks.  Still doable, we just need to get trickier in how we select the previous timeframe.

    WoW Mean (Multiweek) =
    // VAR numWeeks = DISTINCTCOUNT('Table'[Week]) // Selected weeks may not necessarily be continuous, or there may be a week with no data
    VAR numWeeks =  // Better formula for figuring out the timespan, though will still give weird results if you skip a week in slicer (e.g. Week 5,6,8 will compare data from weeks 5,6, and 8 to data from weeks 1-4)
        MAX ( 'Table'[Week] ) - MIN ( 'Table'[Week] ) + 1
    RETURN
        [Weighted Mean]
            - CALCULATE (
                [Weighted Mean],
                FILTER (
                    ALL ( 'Table'[Week] ),
                    'Table'[Week]
                        IN GENERATESERIES ( MIN ( 'Table'[Week] ) - numWeeks, MIN ( 'Table'[Week] ) - 1 )
                )
            )

    I've attached a .pbix that I used.  I added random [Score] data for weeks 53/54, but kept [Resp] the same due to laziness.

    Note that there are many ways to do this filter instead of using IN GENERATESERIES style syntax. 

    My first thought was to use IN ADDCOLUMNS(VALUES('Table'[Week]), "Prev Week", 'Table'[Week]-numWeeks)) style syntax, but I think I GENERATESERIES is an easier/clearer way to do it. This version also had the same issue of handling weeks with no data/filtered out data poorly.

    You could also use 'Table'[Week] >= MIN('Table'[Week])-numWeeks && 'Table'[Week] < MIN('Table'[Week]) style syntax, which may be easier to update, but (I think) is harder to read.   

     

    The point is to get that FILTER statement working for the previous time period's data. Once you do that to change the context properly, the rest should fall into place.