Forum Discussion
How to get rolling Wow values from the tall format data set
- 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.
Hi Cmcmahan
Thanks for posting such an ellaborating way to achieve the result. Earlier, I was trying to use this same DAX expression but with && but it does not works as it gives the result in boolean equation and not whole numbers, therefore not giving the result.
If any suggestion on how to use with &&, please reply. But, the problem is solved with Generateseries. It worked like a charm.
Thanks again for your support.
Dhruv Vats
No problem. It was an interesting problem to solve. We do it all for the kudos and accepted replies.
What part were you using the && in? The filter?
As far as using (value > MIN(other value) && (value < MAX(last value)) style syntax for filtering, you would probably need to wrap that into a FILTER(table, <condition>) setup to work.