Forum Discussion
Solved
YOY week number
I would like to calculate a difference in averages if certain conditions are met:
In this particular example, my data looks something like this:
Then, when I group it by average of conditions (Year and WeekNumber), it looks something like this:
And finally, what I want to do, is to do an average when Year = Year -1 and week number is the same, so I would have something like this:
My approach was to create a formula that was something like:
Delta = CALCULATE ( AVERAGE ( Table[Ratio] ),
FILTER(
ALL ( Table ),
Table[Year] = Table[Year] - 1
&& Table[WeekNumber] = Table[WeekNumber]
)
)
// I know the WeekNumber = WeekNumber part is wrong since it's like 1=1, but
// I would like to know if there is a way to do something similar to thisFor those of you who could have the same question, I found a solution creating a measure like this:
DeltaTest = CALCULATE(AVERAGE(Table[Ratio]), FILTER( ALL(Table), Table[WeekNumber] = SELECTEDVALUE(Table[WeekNumber]) && Table[Year] = SELECTEDVALUE(Table[Year])-1) )and then just another measure that substract
Delta = Table[Ratio] - DeltaTest
1 Reply
For those of you who could have the same question, I found a solution creating a measure like this:
DeltaTest = CALCULATE(AVERAGE(Table[Ratio]), FILTER( ALL(Table), Table[WeekNumber] = SELECTEDVALUE(Table[WeekNumber]) && Table[Year] = SELECTEDVALUE(Table[Year])-1) )and then just another measure that substract
Delta = Table[Ratio] - DeltaTest