Static Percentage Shrinkage

[ConorMcCoy]

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

[lbendlin]

Note that ALL is a rather blunt instrument. Consider if ALLSELECTED is appropriate.

[ConorMcCoy]

Thanks, that was a great help. 

[lbendlin]

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.

[ConorMcCoy · Accepted 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)))

[ConorMcCoy]

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.