Forum Discussion
Helper IHelp Getting Average of Minimum Daily Values for Specified Month
Hello,
I am trying to do a calculation which is the AVERAGE of the MIN temperature values by DAY in the month.
For example, if a user picks 2022/10/20 in the slicer, the measure must go and grab the last 5 years worth of data, for every day in october, calculate the daily MIN for each day, then return the AVERAGE of those values for use on a line chart.
I have included a sample below. The measure I was working with is called Temperature - 5Year Average Daily Min.
PBIX:
https://drive.google.com/file/d/1FF_VLnHGGfUNDfD-ODQ40OiOAmqpcYBC/view?usp=share_link
The results of the measure are expected to be the below values:
| 10.3 | 2022-10-01 |
| 10.36 | 2022-10-02 |
| 10.66 | 2022-10-03 |
| 10.92 | 2022-10-04 |
| 9.56 | 2022-10-05 |
| 12.62 | 2022-10-06 |
| 13.64 | 2022-10-07 |
| 12.32 | 2022-10-08 |
| 12.72 | 2022-10-09 |
| 14.34 | 2022-10-10 |
| 11.38 | 2022-10-11 |
| 9.62 | 2022-10-12 |
| 10.96 | 2022-10-13 |
| 10.26 | 2022-10-14 |
| 8.92 | 2022-10-15 |
| 6.96 | 2022-10-16 |
| 6.18 | 2022-10-17 |
| 6.88 | 2022-10-18 |
| 6.76 | 2022-10-19 |
| 8.2 | 2022-10-20 |
| 8.4 | 2022-10-21 |
| 8.44 | 2022-10-22 |
| 8.98 | 2022-10-23 |
| 6.24 | 2022-10-24 |
| 5.12 | 2022-10-25 |
| 4.54 | 2022-10-26 |
| 6.46 | 2022-10-27 |
| 5.9 | 2022-10-28 |
| 6.16 | 2022-10-29 |
| 4.38 | 2022-10-30 |
| 5.1 | 2022-10-31 |
MCassady , You can use a measure like this which will sum above the day level.
sumx(Values(Table[Date]), calculate(Min(Table[Value])) )
3 Replies
- amitchandak
Super User
MCassady , You can use a measure like this which will sum above the day level.
sumx(Values(Table[Date]), calculate(Min(Table[Value])) )
MCassadyHey amitchandak
I am trying something like this in my measure as seen in the example file. Although I am using averagex instead of sumx because I am looking for the average.
I am also using some extra filters that the other working measure is using.
I am not getting my expected results still.
- MCassady
I figured out what was wrong. I was accidentally including the current year in my "Previous 5 years average".
Just me not being careful enough.