Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
ConorMcCoy
Frequent Visitor

Static Percentage Shrinkage

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.

 

DateScheduled AttendedActual AttendedProductive HoursOOO Shrinkage TrendIdeal Result
01/08/2022296.00181.82165.910.850.20
02/08/20221535.001361.181150.890.210.20
03/08/20221657.001557.871322.480.160.20
04/08/20221588.001530.791323.240.160.20
05/08/20221589.251508.101310.950.150.20
06/08/2022176.75174.99160.480.240.20
07/08/2022149.00151.83141.610.170.20
08/08/20221633.251568.341393.300.160.20
09/08/20221772.831728.811536.010.130.20
10/08/20221783.501753.981553.400.130.20
11/08/20221749.00   0.20
12/08/20221713.50   0.20
13/08/2022146.00   0.20
14/08/2022140.00   0.20
15/08/20221772.00   0.20
16/08/20221819.00   0.20
17/08/20221825.00   0.20
18/08/20221772.50   0.20
19/08/20221701.50   0.20
20/08/2022173.50   0.20
21/08/2022126.50   0.20
22/08/20221823.25   0.20
23/08/20221846.25   0.20
24/08/20221837.00   0.20
25/08/20221748.00   0.20
26/08/20221737.00   0.20
27/08/2022149.50   0.20
28/08/2022110.50   0.20
29/08/20221806.25   0.20
30/08/20221867.25   0.20
31/08/20221864.00   0.20

 

1 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)))

View solution in original post

5 REPLIES 5
lbendlin
Super User
Super User

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

Thanks, that was a great help. 

lbendlin
Super User
Super User

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. 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors