Running Average is not working.

[Zack92]

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

RunningAvg 2 = DIVIDE( [CaseCountRunning_Sum], [Num of Months], 0 )

Sample File

https://drive.google.com/file/d/17px3PCl0P2juOvNMCLTDviHWNrkwmDkc/view?usp=sharing

https://drive.google.com/file/d/1rQYIqZONTy2wd8OL5PxEx35tvgxlhIse/view?usp=sharing

Thank you

[amitchandak]

@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]

@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 

[Zack92]

@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.