Forum Discussion
Create average of previous days data
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 |
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
3 Replies
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
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.