Forum Discussion

bubbaallred's avatar
bubbaallred
Frequent Visitor
3 years ago

Rolling Average on non-summarized data

I'm trying to create a rolling average measure but instead of returning the average of the period, I just get the total for the single date/7.  I've tried multiple measures using the days in period function and days between, all with the same result.  I think this has something to do with the nature of my data.  eg. I have dozens of records each day each from different employees and for different clients.  Power BI has no issue summarizing that data by date and allowing me to filter it by date, client, etc.  Here's one example of my dax measure.  I could give you multiple examples of code I've tried, but they all yielded same result as stated above. 

RollingAvg =
VAR NumDays = 7
VAR RollingSum =
CALCULATE(sum('DATA'[Minutes x]),
DATESINPERIOD('DATA'[Date Received],LASTDATE('DATA'[Date Received]),-NumDays,DAY))
RETURN
RollingSum/NumDays
Need Help

3 Replies

    • bubbaallred's avatar
      bubbaallred
      Frequent Visitor
      3 years ago

      I appreciate your response.  This method returned exactly the same bad result.  For a given date, the rolling average is the same as the sum for that date.

      I did discover when fooling around with it some more that If i simply change the date field in the visual to a Date instead of a Date Hierarchy, all the functions I've tried begin working.  There's something about using that Date Hierarchy which breaks all these rolling average measures... 

      At this point I have the visual working, I just have no idea why that fixed the issue.

      Thanks for the help though! 

  • bubbaallred's avatar
    bubbaallred
    Frequent Visitor
    3 years ago

    UPDATE:  After further review, the problem is caused because I have the applicable date field set to Date Hierarchy.  When I changed that, all the rolling average measures began working.