Forum Discussion

Zack92's avatar
Zack92
Icon for Helper III rankHelper 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 calcula...
Need Help
Show and Tell
Tips and Tricks
amitchandak's avatar
amitchandak
Icon for Super User rankSuper 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
Icon for Helper III rankHelper 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