Forum Discussion

newguy's avatar
newguy
New Member
4 years ago
Solved

Issue With Rolling 3 Month Average

I know there are alot of rolling average posts out there, but I haven't been able to find a solution to my issue. I am trying to get a 3 month rolling average of revenue, example below:   For...
  • tamerj1's avatar
    tamerj1
    4 years ago

    Hi newguy 
    Would you please double check and confirm if the date table is marked as date table. In all cases would you please add REMOVEFILTERS ( D_Date ) inside CALCULATE and let me know if you get any different results. 

newguy's avatar
newguy
New Member
4 years ago

tamerj1 Same issue unfortunately. Let me make sure I am using the correct date fields in your example. Here are some of my date table fields and an example value from the table:

 

*Calendar_Date = 1/1/2022 12:00:00 AM

*Calendar_Month = 1

*Calendar_Year = 2022

*Month_Year = January 2022

*Year_Month = 2022-01

 

I tried switching the SUMMARIZE function to be

SUMMARIZE(D_DATE,D_DATE[Calendar_Month], D_DATE[Calendar_Year])

as well and didnt have any luck.

tamerj1's avatar
tamerj1
Icon for Community Champion rankCommunity Champion
4 years ago

newguy 

What are you expecting to see at the year level?

  • newguy's avatar
    newguy
    New Member
    4 years ago

    tamerj1 I really don't need it at the year level, only the month. Each month should show the average of the Net Revenue of the previous three months. So May would show the average of March/April/May.

     

    Ideally I would have a card showing the 3 month average for the most recent month, or a line/bar combo graph showing the Total Revenue using the bars, and then the line would be the rolling average (something like the image below).

     

    This specific calculation would also be used in another measure where I would take total payments in a given month, then divide it by the 3 month average net revenue to get a collection rate. Thanks!

     

     

    • tamerj1's avatar
      tamerj1
      Icon for Community Champion rankCommunity Champion
      4 years ago

      newguy 

      So your measure is calculating the correct values. Then where is the problem?

      • newguy's avatar
        newguy
        New Member
        4 years ago

        tamerj1 My apologies, the excel screenshot was just an image I found on google of an example of what I am trying to do - not my actual data/measures. In my Power BI file I am still having the previous issue. Here is a screenshot of where I am currently at, where Net Revenue R3M is the first attempt you sent me to fix the problem, and Net Revenue R3Mv2 is the second attempt.