Forum Discussion

Syndicate_Admin's avatar
Syndicate_Admin
Icon for Administrator rankAdministrator
3 years ago

Get average cumulative amount.

Hello. Pordrian support me. At present I want to average the accumulated amounts according to the selection of the months in my monthly segmenter. In the image you can see that I have selected 3 ...
amitchandak's avatar
amitchandak
Icon for Super User rankSuper User
3 years ago

Syndicate_Admin , for last three you need a measure like

 

Rolling 3 = CALCULATE(sum(Sales[Sales Amount]),DATESINPERIOD('Date'[Date],MAX('Date'[Date]),-3,MONTH))

 

Rolling 3 = CALCULATE([Net], WINDOW(-2,REL, 0, REL, ADDCOLUMNS(ALLSELECTED('Date'[Month Year],'Date'[Month Year Sort] ),ORDERBY([Month Year Sort],asc)))

 

for Avg

 

3 Month Avg = CALCULATE(AverageX(Values('Date'[MONTH Year]),calculate(Sum('Table'[Value])))
,DATESINPERIOD('Date'[Date ],MAX('Date'[Date ]),-3,MONTH))

 

Rolling Months Formula: https://youtu.be/GS5O4G81fww

 

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