Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
Hi All,
So The data I'm working with needs to be averaged, but we work under the premise there are 13 periods in a year (So can't use months or weeks for instance)
So The Data I have:
YearPeriod | Sick % | SickMAA % |
2019-01 | 4.90% | |
2019-02 | 4.26% | |
2019-03 | 4.30% | |
2019-04 | 4.16% | |
2019-05 | 3.93% | |
2019-06 | 3.92% | |
2019-07 | 4.08% | |
2019-08 | 4.30% | |
2019-09 | 4.14% | |
2019-10 | 4.28% | |
2019-11 | 3.85% | |
2019-12 | 3.65% | |
2019-13 | 2.74% | |
2020-01 | 3.73% | |
2020-02 | 3.35% | |
2020-03 | 7.49% | |
2020-04 | 8.57% | |
2020-05 | 4.60% | |
2020-06 | 4.53% | |
2020-07 | 3.92% | |
2020-08 | 3.80% | |
2020-09 | 3.15% | |
2020-10 | 3.70% | |
2020-11 | 4.37% | |
2020-12 | 5.38% | |
2020-13 | 3.16% |
I have tried all manner of rolling period calculations but they all seem to want to use date, but I need it to calculate based on the last 13 periods (column A) which are Text
I've googled until the cows come home, I'm hoping someone out there has a fix for this 😞
Solved! Go to Solution.
@Anonymous okay I think I managed to solve this. But you need some sort of primary key in the dataset
Measure20 =
VAR _lower =
MAXX ( MAASUMMARY, MAASUMMARY[Index] )
VAR _upper = _lower - 12
VAR _2 =
IF (
MAXX ( MAASUMMARY, MAASUMMARY[Index] ) - 13 >= 0,
CALCULATE (
SUMX ( MAASUMMARY, MAASUMMARY[Sick %] ),
ALL ( MAASUMMARY ),
MAASUMMARY[Index] >= _upper
&& MAASUMMARY[Index] <= _lower
)
) / 13
RETURN
_2
@Anonymous okay I think I managed to solve this. But you need some sort of primary key in the dataset
Measure20 =
VAR _lower =
MAXX ( MAASUMMARY, MAASUMMARY[Index] )
VAR _upper = _lower - 12
VAR _2 =
IF (
MAXX ( MAASUMMARY, MAASUMMARY[Index] ) - 13 >= 0,
CALCULATE (
SUMX ( MAASUMMARY, MAASUMMARY[Sick %] ),
ALL ( MAASUMMARY ),
MAASUMMARY[Index] >= _upper
&& MAASUMMARY[Index] <= _lower
)
) / 13
RETURN
_2
@smpa01 So I added the index (Via edit query) and copied your solution but I'm still getting all rows showing 4.60%
Is there anything that would be blocking this?
@Anonymouspbix is attached
https://drive.google.com/file/d/1c32w8A-ERY3VW4TnLMs1EFAy_6rJPmmP/view?usp=sharing
So I had been an idiot and put it in as a column, not a measure *Facepalm*
THANK YOU!!
The CLOSEST I have got so far is with this: