Get average cumulative amount.

[Syndicate_Admin]

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 months and the amount I want to get on my card is the sum of the amounts of the 3 months that would be: 6,723,015.03, instead I am getting the amount of the last month selected, They could support me to modify the measure to achieve my goal.

The measure I am using is as follows:

ALTERNATIVE 1:

***************************************************************************

Cumulative = CALCULATE(SUM('Working capital'[Working capital]),

WINDOW(1, ABS, 0, REL, ALLSELECTED(Tabla_calendario[Date])

), 'Working capital'[account_id] IN {12123}

)

ALTERNATIVE 2:

*****************************************************************************

where SFC = CALCULATE(SUM('Working capital', or [working capital]),

FILTER( ALL(Tabla_calendario),

Tabla_calendario[Date]<=.MAX(Tabla_calendario[Date])

), 'Working capital'[account_id] IN {12123}

)

I show you the visual of the results I get.

[amitchandak]

@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