Forum Discussion
Rolling 28 Day Measure
A version of this question has been asked numerous times, but I still cannot figure it out from all the other examples. I am trying to calculate a rolling 28 day count of responses.
A view of my data set is below, as is my current formula for the measure. What I thought this would do was sum up the count of responses across the last 28 days, but all it seems to return is the latest day values. In addition, I want the measure to be responsive to additional filters from the data set below - IsUserInternal and Service. It seems to be doing that, but, again, only showing the counts for the specific day
Thanks in advance.
28DResponses = CALCULATE(sum('Response Daily Counts'[Responses]),DATESINPERIOD('Response Daily Counts'[EventDate],LASTDATE('Response Daily Counts'[EventDate]),28,DAY))
3 Replies
- Greg_DecklerCommunity Champion
Please post data in text form. Please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490
See if my Time Intelligence the Hard Way provides a different way of accomplishing what you are going for.
https://community.powerbi.com/t5/Quick-Measures-Gallery/Time-Intelligence-quot-The-Hard-Way-quot-TITHW/m-p/434008- jofosterMicrosoft Employee
Thanks for the tips. I copied the data into the post. I could not figure out how to add attachments...perhaps that is not allowed in this forum. Thanks for your help. I looked at your other thing and I could not make the jump to solve my problem.
- jofosterMicrosoft Employee
Here is my data again in an easier format to consume.
EventDate Service IsUserInternal Prompts Responses CommentCount 2019-10-22 00:00:00.0000000 Boards 0 1296 91 13 2019-10-22 00:00:00.0000000 Boards 1 247 12 1 2019-10-23 00:00:00.0000000 Boards 0 1233 90 10 2019-10-23 00:00:00.0000000 Boards 1 196 9 0 2019-10-24 00:00:00.0000000 Boards 0 1200 93 14 2019-10-24 00:00:00.0000000 Boards 1 205 5 1 2019-10-25 00:00:00.0000000 Boards 0 1055 75 14 2019-10-25 00:00:00.0000000 Boards 1 193 5 1 2019-10-26 00:00:00.0000000 Boards 0 70 11 4 2019-10-26 00:00:00.0000000 Boards 1 38 1 0 2019-10-27 00:00:00.0000000 Boards 0 105 7 2 2019-10-27 00:00:00.0000000 Boards 1 13 0 0 2019-10-28 00:00:00.0000000 Boards 0 1286 92 16 2019-10-28 00:00:00.0000000 Boards 1 175 3 0 2019-10-29 00:00:00.0000000 Boards 0 1232 93 11 2019-10-29 00:00:00.0000000 Boards 1 206 11 0 2019-10-30 00:00:00.0000000 Boards 0 1280 93 18 2019-10-30 00:00:00.0000000 Boards 1 192 10 3 2019-10-31 00:00:00.0000000 Boards 1 204 9 1 2019-10-31 00:00:00.0000000 Boards 0 1196 89 8 2019-11-01 00:00:00.0000000 Boards 0 887 66 12 2019-11-01 00:00:00.0000000 Boards 1 188 6 0 2019-11-02 00:00:00.0000000 Boards 0 77 4 1 2019-11-02 00:00:00.0000000 Boards 1 23 1 0 2019-11-03 00:00:00.0000000 Boards 0 106 7 0 2019-11-03 00:00:00.0000000 Boards 1 16 1 0 2019-11-04 00:00:00.0000000 Boards 0 1358 92 9 2019-11-04 00:00:00.0000000 Boards 1 216 9 1 2019-11-05 00:00:00.0000000 Boards 1 228 4 0 2019-11-05 00:00:00.0000000 Boards 0 1368 120 18 2019-11-06 00:00:00.0000000 Boards 0 1250 88 17 2019-11-06 00:00:00.0000000 Boards 1 216 10 1 2019-11-07 00:00:00.0000000 Boards 0 1250 87 20 2019-11-07 00:00:00.0000000 Boards 1 205 8 0 2019-11-08 00:00:00.0000000 Boards 0 1069 78 16 2019-11-08 00:00:00.0000000 Boards 1 180 9 1 2019-11-09 00:00:00.0000000 Boards 1 29 0 0 2019-11-09 00:00:00.0000000 Boards 0 81 6 3 2019-11-10 00:00:00.0000000 Boards 1 15 2 0 2019-11-10 00:00:00.0000000 Boards 0 99 9 1 2019-11-11 00:00:00.0000000 Boards 1 211 7 0 2019-11-11 00:00:00.0000000 Boards 0 1219 73 11 2019-11-12 00:00:00.0000000 Boards 0 1359 104 20 2019-11-12 00:00:00.0000000 Boards 1 204 8 2 2019-11-13 00:00:00.0000000 Boards 0 1284 84 19 2019-11-13 00:00:00.0000000 Boards 1 199 10 2 2019-11-14 00:00:00.0000000 Boards 0 1218 100 17 2019-11-14 00:00:00.0000000 Boards 1 215 5 1 2019-11-15 00:00:00.0000000 Boards 1 162 3 0 2019-11-15 00:00:00.0000000 Boards 0 1000 78 13 2019-11-16 00:00:00.0000000 Boards 0 108 12 1 2019-11-16 00:00:00.0000000 Boards 1 32 2 1 2019-11-17 00:00:00.0000000 Boards 0 112 9 0 2019-11-17 00:00:00.0000000 Boards 1 23 2 0