Forum Discussion
Helper IIGet each month total and average based on date
hello All,
I need help on calculating the monthly total and average for the data.
i have the data like below
| ID | Date | Days |
| 163 | 2/28/2020 7:37 | 3 |
| 164 | 2/25/2020 12:47 | 0 |
| 165 | 3/2/2020 15:43 | 5 |
| 166 | 3/3/2020 4:09 | 6 |
| 173 | 3/3/2020 12:35 | 0 |
| 174 | 3/19/2020 19:46 | 16 |
| 175 | 3/19/2020 19:50 | 16 |
| 178 | 3/4/2020 14:27 | 0 |
| 179 | 3/4/2020 14:34 | 0 |
| 180 | 3/4/2020 14:36 | 0 |
| 181 | 3/4/2020 15:59 | 2 |
| 182 | 3/4/2020 15:59 | 8 |
| 185 | 3/10/2020 14:08 | 4 |
| 186 | 3/10/2020 14:08 | 4 |
| 187 | 3/10/2020 14:51 | 0 |
| 188 | 3/10/2020 14:53 | 3 |
| 189 | 3/11/2020 16:33 | 3 |
| 190 | 3/11/2020 16:41 | 0 |
| 191 | 3/12/2020 8:00 | 0 |
| 192 | 3/12/2020 8:01 | 0 |
| 195 | 3/12/2020 16:58 | 1 |
| 196 | 3/12/2020 17:01 | 1 |
| 197 | 3/13/2020 8:05 | 0 |
| 198 | 3/13/2020 8:06 | 0 |
| 199 | 3/13/2020 13:19 | 0 |
| 200 | 3/13/2020 13:10 | 0 |
| 201 | 3/27/2020 11:02 | 9 |
| 202 | 3/27/2020 11:02 | 15 |
| 204 | 4/16/2020 19:52 | 6 |
| 205 | 4/16/2020 19:49 | 6 |
| 206 | 5/6/2020 21:13 | 6 |
| 207 | 5/6/2020 21:12 | 6 |
need to get the output as below
| Month | Total | Average |
| Jan-20 | 0 | 0 |
| Feb-20 | 3 | 3 |
| Mar-20 | 93 | 0.2 |
| Apr-20 | 12 | 6 |
| May-20 | 12 | 6 |
| Jun-20 | 0 | 0 |
| Jul-20 | 0 | 0 |
| Aug-20 | 0 | 0 |
| Sep-20 | 0 | 0 |
| Oct-20 | 0 | 0 |
| Nov-20 | 0 | 0 |
| Dec-20 | 0 | 0 |
the calculation is for the month Jan 2020. i dont have any records, so the value is zero.
for the month feb 2020, i see two records (2/28/2020 and 2/25/2020 with valuse 3 and 0), here the total is 3+0=3
and the average is 3/1 = 3.
and also same for April, the total is 6+6 =12 and the average is 12/2 = 6.
Can anyone help me with this?
Thanks
18 Replies
usomarajuHi Sturla,
Thank you for your quick response, but when i used same formulas i see same result for all months.
the steps i followed, on my table, by using measure i applied the below formula
Total number of days = var _s = SUM('Table'[Days]) returnIF(ISBLANK(_s),0,_s)which is displaying same no of days as in total no of daysand same for average also.I 'm not sure where i missed.thanks- sturlaws
Resident Rockstar
Did you create a date-table? And a relationship between the date table and your main table?
