Create average of previous days data

[Anonymous]

Hi,

I have a table which has workout data for diffrent devices for each day. What I want is to get a threshold value which is 30% of the sum of the previous days.

So if you see below you will see sample data. Here I have workout per partner for the last 5 days, so I need to create a column/measure which will create a threshold having formula - 

Threshold = 0.3*(workout for 20th+19th+18th)

Then I will create a compare that if workout for 21st < threshold then highlight the cell as red or have staus as "unhealthy". 

Can anyone help. 

Partner Name	Workout Date	Total Workouts
APPLE	17/10/2019 00:00	1463490
APPLE	18/10/2019 00:00	1436614
APPLE	19/10/2019 00:00	1341512
APPLE	20/10/2019 00:00	1356111
APPLE	21/10/2019 00:00	1494047
FITBIT	17/10/2019 00:00	552295
FITBIT	18/10/2019 00:00	370275
FITBIT	19/10/2019 00:00	371072
FITBIT	20/10/2019 00:00	377244
FITBIT	21/10/2019 00:00	494469
GARMIN	17/10/2019 00:00	585582
GARMIN	18/10/2019 00:00	547174
GARMIN	19/10/2019 00:00	575836
GARMIN	20/10/2019 00:00	597525
GARMIN	21/10/2019 00:00	597247

[YJ · Accepted solution]

Hi,

just for fun i added in another partner, to show the case when below threshold:

Above is the visual and below is the working columns. for example sake i used date as today()-1 (21st oct)

I make sure of main cal columns, prehaps a more efficient way would be to use measure, i look forward to other better solution, meanwhile i hope this ans your question.

attached PBIX: PBIX 

regards

[Anonymous]

Hi @YJ ,

I had to change my connection mode to DirectQuery mode and now the functions you provided are not working. Can you help please. 

[Anonymous]

Hi,

Thank you so much for this. Just had to make some small tweeks in the threshold formula and I was able to achieve my requirement.