Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Hi All,
I need to get a rolling 28 day % figure for shrinkage - i.e. shrinkage/attended.
I need this figure to be static such that it does not change based on the date that is being displayed in the table. Below is what I am currently getting.
My current formula is just giving me the figure for that day's shrinkage. What I need it to do is show me the shrinkage figure for the period ending yesterday and starting 28 days ago.
The formula I have built for this is;
CALCULATE(([In Office Shrinkage]/[Actual Attended]),FILTER(VALUES('Agent Data'[Date]),'Agent Data'[Date]<=TODAY()-1),FILTER(VALUES('Agent Data'[Date]),'Agent Data'[Date]>=TODAY()-28))
Ideally, I would want the formula to output as displayed in the column on the far right and this would be the case whether or not the date in the table was outside of the 28 day context.
Date | Scheduled Attended | Actual Attended | Productive Hours | OOO Shrinkage Trend | Ideal Result |
01/08/2022 | 296.00 | 181.82 | 165.91 | 0.85 | 0.20 |
02/08/2022 | 1535.00 | 1361.18 | 1150.89 | 0.21 | 0.20 |
03/08/2022 | 1657.00 | 1557.87 | 1322.48 | 0.16 | 0.20 |
04/08/2022 | 1588.00 | 1530.79 | 1323.24 | 0.16 | 0.20 |
05/08/2022 | 1589.25 | 1508.10 | 1310.95 | 0.15 | 0.20 |
06/08/2022 | 176.75 | 174.99 | 160.48 | 0.24 | 0.20 |
07/08/2022 | 149.00 | 151.83 | 141.61 | 0.17 | 0.20 |
08/08/2022 | 1633.25 | 1568.34 | 1393.30 | 0.16 | 0.20 |
09/08/2022 | 1772.83 | 1728.81 | 1536.01 | 0.13 | 0.20 |
10/08/2022 | 1783.50 | 1753.98 | 1553.40 | 0.13 | 0.20 |
11/08/2022 | 1749.00 | 0.20 | |||
12/08/2022 | 1713.50 | 0.20 | |||
13/08/2022 | 146.00 | 0.20 | |||
14/08/2022 | 140.00 | 0.20 | |||
15/08/2022 | 1772.00 | 0.20 | |||
16/08/2022 | 1819.00 | 0.20 | |||
17/08/2022 | 1825.00 | 0.20 | |||
18/08/2022 | 1772.50 | 0.20 | |||
19/08/2022 | 1701.50 | 0.20 | |||
20/08/2022 | 173.50 | 0.20 | |||
21/08/2022 | 126.50 | 0.20 | |||
22/08/2022 | 1823.25 | 0.20 | |||
23/08/2022 | 1846.25 | 0.20 | |||
24/08/2022 | 1837.00 | 0.20 | |||
25/08/2022 | 1748.00 | 0.20 | |||
26/08/2022 | 1737.00 | 0.20 | |||
27/08/2022 | 149.50 | 0.20 | |||
28/08/2022 | 110.50 | 0.20 | |||
29/08/2022 | 1806.25 | 0.20 | |||
30/08/2022 | 1867.25 | 0.20 | |||
31/08/2022 | 1864.00 | 0.20 |
Solved! Go to Solution.
I've actually figured it out. I needed to use the ALL function so the measure would ignore the filter context in the visual. The formula I've used is;
(CALCULATE([In Office Shrinkage],all('Agent Data'),DATESINPERIOD('Agent Data'[Date],today()-1,-27,DAY)))/(CALCULATE([Actual Attended],all('Agent Data'),DATESINPERIOD('Agent Data'[Date],today()-1,-27,DAY)))
Note that ALL is a rather blunt instrument. Consider if ALLSELECTED is appropriate.
Thanks, that was a great help.
I need to get a rolling 28 day % figure for shrinkage - i.e. shrinkage/attended.
I need this figure to be static such that it does not change based on the date that is being displayed in the table.
My understanding is that these two requirements are mutually exclusive. Please clarify.
I've actually figured it out. I needed to use the ALL function so the measure would ignore the filter context in the visual. The formula I've used is;
(CALCULATE([In Office Shrinkage],all('Agent Data'),DATESINPERIOD('Agent Data'[Date],today()-1,-27,DAY)))/(CALCULATE([Actual Attended],all('Agent Data'),DATESINPERIOD('Agent Data'[Date],today()-1,-27,DAY)))
So, let's say for the last 28 days up to today we have had a shrinkage percentage of 18%. I need to be able to apply this 18% as an assumption for future days in their shrinkage calculations. Perhaps the use of the word static was mistaken.
By rolling 28 days I mean I need this figure to change based on the current date.
User | Count |
---|---|
42 | |
28 | |
23 | |
18 | |
15 |
User | Count |
---|---|
54 | |
35 | |
18 | |
18 | |
15 |