Forum Discussion

jofoster's avatar
jofoster
Microsoft Employee
6 years ago

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))

 
 

 
Need Help

3 Replies

    • jofoster's avatar
      jofoster
      Microsoft Employee
      6 years ago

      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.

  • jofoster's avatar
    jofoster
    Microsoft Employee
    6 years ago

    Here is my data again in an easier format to consume.  

     

    EventDateServiceIsUserInternalPromptsResponsesCommentCount
    2019-10-22 00:00:00.0000000Boards012969113
    2019-10-22 00:00:00.0000000Boards1247121
    2019-10-23 00:00:00.0000000Boards012339010
    2019-10-23 00:00:00.0000000Boards119690
    2019-10-24 00:00:00.0000000Boards012009314
    2019-10-24 00:00:00.0000000Boards120551
    2019-10-25 00:00:00.0000000Boards010557514
    2019-10-25 00:00:00.0000000Boards119351
    2019-10-26 00:00:00.0000000Boards070114
    2019-10-26 00:00:00.0000000Boards13810
    2019-10-27 00:00:00.0000000Boards010572
    2019-10-27 00:00:00.0000000Boards11300
    2019-10-28 00:00:00.0000000Boards012869216
    2019-10-28 00:00:00.0000000Boards117530
    2019-10-29 00:00:00.0000000Boards012329311
    2019-10-29 00:00:00.0000000Boards1206110
    2019-10-30 00:00:00.0000000Boards012809318
    2019-10-30 00:00:00.0000000Boards1192103
    2019-10-31 00:00:00.0000000Boards120491
    2019-10-31 00:00:00.0000000Boards01196898
    2019-11-01 00:00:00.0000000Boards08876612
    2019-11-01 00:00:00.0000000Boards118860
    2019-11-02 00:00:00.0000000Boards07741
    2019-11-02 00:00:00.0000000Boards12310
    2019-11-03 00:00:00.0000000Boards010670
    2019-11-03 00:00:00.0000000Boards11610
    2019-11-04 00:00:00.0000000Boards01358929
    2019-11-04 00:00:00.0000000Boards121691
    2019-11-05 00:00:00.0000000Boards122840
    2019-11-05 00:00:00.0000000Boards0136812018
    2019-11-06 00:00:00.0000000Boards012508817
    2019-11-06 00:00:00.0000000Boards1216101
    2019-11-07 00:00:00.0000000Boards012508720
    2019-11-07 00:00:00.0000000Boards120580
    2019-11-08 00:00:00.0000000Boards010697816
    2019-11-08 00:00:00.0000000Boards118091
    2019-11-09 00:00:00.0000000Boards12900
    2019-11-09 00:00:00.0000000Boards08163
    2019-11-10 00:00:00.0000000Boards11520
    2019-11-10 00:00:00.0000000Boards09991
    2019-11-11 00:00:00.0000000Boards121170
    2019-11-11 00:00:00.0000000Boards012197311
    2019-11-12 00:00:00.0000000Boards0135910420
    2019-11-12 00:00:00.0000000Boards120482
    2019-11-13 00:00:00.0000000Boards012848419
    2019-11-13 00:00:00.0000000Boards1199102
    2019-11-14 00:00:00.0000000Boards0121810017
    2019-11-14 00:00:00.0000000Boards121551
    2019-11-15 00:00:00.0000000Boards116230
    2019-11-15 00:00:00.0000000Boards010007813
    2019-11-16 00:00:00.0000000Boards0108121
    2019-11-16 00:00:00.0000000Boards13221
    2019-11-17 00:00:00.0000000Boards011290
    2019-11-17 00:00:00.0000000Boards12320