Forum Discussion

Zack92's avatar
Zack92
Helper III
2 years ago

Running Average is not working.

Hello 

I'm trying to figure out the running average for Open Case Numbers (ones with a status that's not Closed), but I'm totally off. Supposedly, this month should be around 725, but my calculations are nowhere near that. I'm basically trying to track the number of cases piling up (Case Backlog). Can anyone lend a hand or point me in the right direction to get this done?
 
I think I might have figured out where I got stuck. It seems like some of these cases were actually opened a few months back and are still hanging around, not closed yet.
 
Method 1 
RunningAvg 1 =
VAR minimumdate =
    CALCULATE (
        MIN ( df_Case_Running_Test[CreatedDate] ),
        ALL ( df_Case_Running_Test[CreatedDate]  )
    )

VAR totalhour =
    CALCULATE (
        CALCULATE (
            Count ( df_Case_Running_Test[CaseNumber])
        ),
        FILTER (
            ALL ( df_Case_Running_Test[CreatedDate] ),
            df_Case_Running_Test[CreatedDate] >= MIN ( df_Case_Running_Test[CreatedDate])
        )
    )
RETURN
    IF (
        MIN ( df_Case_Running_Test[CreatedDate] ) <= minimumdate,
        Count (df_Case_Running_Test[CaseNumber] ) )
Method 2
Need Help
Show and Tell
Tips and Tricks

3 Replies

  • amitchandak's avatar
    amitchandak
    Super User
    2 years ago

    Zack92 , You need try a measure like this with help from date table joined with date of your table

     

    Cumm Sales = CALCULATE(AverageX(Values('Date'[Month Year]), CALCULATE(Count(df_Case_Running_Test[CaseNumber]))), filter(all('Date'),'Date'[date] <=max('Date'[date])))

     

     

    You can also use window function 

     

    Cumm Based on Date = CALCULATE(AverageX(Values('Date'[Month Year]), CALCULATE(Count(df_Case_Running_Test[CaseNumber]))) , Window(1,ABS,0,REL, ALL('date'[date]),ORDERBY('Date'[date],ASC)))

     

     

    Continue to explore Power BI Window function Rolling, Cumulative/Running Total, WTD, MTD, QTD, YTD, FYTD: https://youtu.be/nxc_IWl-tTc
    https://medium.com/@amitchandak/power-bi-window-function-3d98a5b0e07f

    • Zack92's avatar
      Zack92
      Helper III
      2 years ago

      amitchandak 
      Thank you so much for the quick response. So I followed your instructions, created a "Date" table, and joined it to "df_Case_Running_Test". My numbers are really low, I am expecting for the current month around 725 and all other previous months between 600 and 700 average cases. 

      Is that because I am missing "[Month Year]" in the formula? How do I add "[Month Year]"
      One last question, how do I filter everything case is "Open" not "Closed"? 
      Any closed cases should not count toward the average. 

      Below is the file with the changes
      https://drive.google.com/file/d/1G8CwOJh_BGUsMUZ41ZUYjzT_cPXvOz9p/view?usp=sharing 

       

      Again, thank you so much for your help & time. 

       

    • Zack92's avatar
      Zack92
      Helper III
      2 years ago

      amitchandak 
      Hello, I figured out the "Moth Year", but I am getting really high number. Any suggestions as to why is that? 

       

      Cumm Sales = CALCULATE(AverageX(Values('Date'[Month]), CALCULATE(Count(df_Case_Running_Test[CaseNumber]))), filter(all('Date'),'Date'[date] <=max('Date'[date])))
       

      Thank you