Accumulating Average over past 12 months

[Winter_king94]

Hi all, 

I'm stumped. I am trying to create a view where based on the current month, a line chart will calculate the accumulating average for the previous 12 months. 

For example, the current month is June 2025, so I want to see the accumulating average for Jun 2024- May 2025. I don't want to have to change a slicer to get this to update, I want the measure to update itself each month.

For the accumulating average, I want to have the measure sum together the count of Days to Close and then divide that by how many months were accumulated. For example- (Jun'24 +Jul'24)/ 2. 

Below is an image in excel of what I'm trying to get at: 

In Power BI, I've only been able to get the rolling 12 months average (but it shows all the months and does not limit to only 12 months)

Avg Days to Close Moving12MO =

    VAR NumOfMonths = 12

    VAR _Maxdate = MAX('Date'[Date]) /*6/5/25*/

    VAR LastCurrentDate = EOMONTH(_Maxdate, -1) /*5/31/2025*/

    VAR _StartDate = EOMONTH(_maxdate, -12) +1 /*6/1/2024*/

    VAR Period =  DATESINPERIOD ( 'Date'[Date], LastCurrentDate, - NumOfMonths, MONTH )

    VAR Result =

        CALCULATE(

            AVERAGEX(      

                    VALUES('Date'[Month And Year]) /*allows you to get the average for each month*/    

                    , 'Opportunity'[Days2Close Test]        

                    ),      

                DATESBETWEEN('Date'[Date], _StartDate, _Maxdate)        

                )      

    RETURN

    Result

'Date' is marked as a date table. 

[v-echaithra]

Hi @Winter_king94 ,

Thank you for reaching out to Microsoft Community.
Try with this DAX 

Accumulating_avg =
VAR Today = TODAY()
VAR Lastfullmonth = EOMONTH(Today, -1)
VAR StartDate = EOMONTH(Lastfullmonth, -11) + 1

VAR MonthsInScope =FILTER (ADDCOLUMNS (SUMMARIZE ( 'Date', 'Date'[YearMonth] ),
"@MonthEnd", EOMONTH (MIN ('Date'[Date] ), 0)),[@MonthEnd] >= StartDate && [@MonthEnd] <= LastFullMonth)

VAR Result =AVERAGEX (MonthsInScope,
VAR Currentmonthend = [@MonthEnd]
VAR Currentmonthstart = EOMONTH(Currentmonthend, 0) - DAY(Currentmonthend) + 1

VAR Datatillthismonth =CALCULATETABLE ('Opportunity',
'Date'[Date] >= StartDate && 'Date'[Date] <= Currentmonthend)

VAR Totaldays = CALCULATE (SUM ( 'Opportunity'[Days2Close Test] ),Datatillthismonth)

VAR MonthsSoFar =CALCULATE (DISTINCTCOUNT ( 'Date'[YearMonth] ),
'Date'[Date] >= StartDate && 'Date'[Date] <= Currentmonthend)
RETURN DIVIDE ( TotalDays, Monthssofar ))
RETURN Result

additionally format your month for the chart like Month = FORMAT('Date'[Date], "yyyy MMM").

If our response addressed, please mark it as Accept as solution and click Yes if you found it helpful.

Regards,

Chaithra.

[Winter_king94]

This unfortunately isn't working for me. The measure is coming back blank.
I took your DAX and tried to tweak it but to no avail. What am I doing wrong? 
*Also wanted to mention that there is an inactive relationship between the Opportunity table and the Date table. The relationship is 'Opportunity'[CloseDate_CDT] to the 'Date'[Date] field. 

Avg Days to Close Moving12MO_3 =

    VAR _Maxdate = MAX('Date'[Date]) /*6/5/25*/

    VAR LastFullMonth = EOMONTH(_Maxdate, -1) /*5/31/2025- returns last day of the previous month*/

    VAR _StartDate = EOMONTH(_maxdate, -12) +1 /*6/1/2024*/

    VAR _Days2Close =CALCULATE (SUM('Opportunity'[Days to Close]), USERELATIONSHIP('Date'[Date], 'Opportunity'[CloseDate_CDT]))

    VAR MonthsInScope =FILTER (

            ADDCOLUMNS (

                SUMMARIZE (

                     'Date', /*table to summarize*/

                     'Date'[Month & Year] ) /*columns to group by*/

                ,"@MonthEnd",  /*NEW COLUMN name*/

                EOMONTH (MIN ('Date'[Date] ), 0)), /*expression to create new column*/

            [@MonthEnd] >= _StartDate && [@MonthEnd] <= LastFullMonth)

    VAR Result =AVERAGEX (MonthsInScope,

        VAR Currentmonthend = [@MonthEnd]

        VAR Currentmonthstart = EOMONTH(Currentmonthend, 0) - DAY(Currentmonthend) + 1 /*give the last day of the month, then subrtact the number of days and add 1*/

        VAR Datatillthismonth =CALCULATETABLE ('Opportunity','Opportunity'[CloseDate_CDT] >= _StartDate && 'Opportunity'[CloseDate_CDT] <= Currentmonthend) /*getting all opportunities with a close date between the date parameters*/

        VAR Totaldays = CALCULATE (_Days2Close, Datatillthismonth)

        VAR MonthsSoFar =CALCULATE (DISTINCTCOUNT ( 'Date'[Month & Year] ),'Date'[Date] >= _StartDate && 'Date'[Date] <= Currentmonthend)

        RETURN DIVIDE ( TotalDays, Monthssofar ))

    RETURN

     Result

[Ashish_Excel]

Hi,

Share some data to work with and show the expected result.  Share data in a format that can be pasted in an MS Excel file.

[Winter_king94]

The screenshot of the excel with the chart is the data & expected result. Is there more you need outside of that? The year/month and sum of days to close is the raw data. The 'Accumulating Average' column is the desired result.

[Ashish_Excel]

Share data in a format that can be pasted in an MS Excel file.  Is the FY June to May i.e. is that the order in which you want to see the months?  Isn't there a proper date column in your dataset?

[Winter_king94]

The table below is a snippet of raw data from the Opportunity table from 2024- 2025. It has the number of days it took each opportunity to close and the date it closed. The Opportunity table has an inactive relationship with the Date table. 'Opportunity'[ClosedDate_CDT] --> 'Date'[Date].

If I am looking at the report on 6/12/2025 (which is not a finished month), I want to see the previous 12 months and their accumulating average. The first month that should show in the visual is 6/2024 and the last month is 5/2025. If I look at this report in August 2025, I want to see the data for 8/2024-7/2025. Hopefully that's what you were looking for.

opportunityid	CloseDate_CDT	Days to Close
D94CF3E9-73B9-411F-85FC-AC24F06400CF	10/24/2024	647
827DCF94-2648-4DC4-AA1D-39BC3C60BA2B	11/13/2024	79
24BA3194-72A4-4A4D-8FC7-CDBA57D7D0F7	12/6/2024	212
627BAEBD-D48A-4CF2-8F66-136C2229BA77	8/8/2024	766
168895D9-B8CD-4B55-9A31-C14ED33BC0B6	3/19/2024	404
EE7267CB-77AB-486F-910B-116D92297311	3/31/2024	971
A40DCA2C-CE71-46CC-86D8-94408BD1B738	12/4/2024	1133
0B2562E1-0113-498D-96CB-F9E1B8CBEC34	5/14/2024	426
3767D7E5-D670-4C82-95E4-3F1E10BD919A	9/11/2024	121
9CED3EA9-210F-4645-A443-284A8C4AD05D	8/8/2024	150
BD3CBF08-9A1A-4FFA-A2CB-A59127809152	9/16/2024	589
FC646680-6165-4705-85F8-A7986A17494E	9/16/2024	432
7F03B000-8D55-4F50-9E03-680F10AC8784	5/15/2024	950
3CB9B83C-83E8-EF11-A731-000D3A37A392	4/24/2025	73
657BDFD2-F404-F011-BAE3-000D3A36199C	5/15/2025	58
B534341A-51E6-49A3-BFEC-1E10478F701C	10/13/2024	544
AFE3039E-2CED-4FB3-9E72-1EFA97154F0C	5/5/2024	520
69704B54-7A0F-4547-8A35-42D6648C194D	3/4/2024	252
C407BADE-E1AD-4DE4-9A7A-A6B3747136EE	12/11/2024	43
A862AF7C-9446-47B0-BE78-2C1BCE014197	12/11/2024	43
934BBD19-A53C-4091-93AD-77F2CA364AFD	11/5/2024	7
CA30D797-D11D-4222-B6E2-5B5D9140AE48	6/19/2024	709
64111240-8866-4C94-A532-A9841CF4638B	6/8/2025	222
76EA7EF4-7C3F-4000-A64A-A6FA400C5155	10/28/2024	4
424C8560-23C5-4592-87BD-3E109C95D94C	10/24/2024	560
003F8125-D721-F011-998A-000D3A36199C	5/4/2025	10
9ED94E23-50F3-45B8-8727-09F13BE0087C	2/1/2024	764
A04F4B6E-0208-47C6-A1CA-F7140E146662	5/16/2024	9
DE922F18-FA67-41E0-8E49-E3DA8331D94A	8/8/2024	127
F1EBB8A5-7623-F011-8C4D-6045BD025238	6/10/2025	45
4F29EF79-2495-4114-889D-64A9E40A2DAF	1/8/2025	320
15CDB247-5EC4-4902-B450-AA40ED218365	12/3/2024	343
5E2BAB98-778B-4549-86C0-C5311DAA9B52	12/5/2024	175
DD8C91E7-0E67-4D8E-AC92-97DB2925D373	8/12/2024	810
5F953C1F-2CDD-42A2-BDD2-A69E3D060F59	9/11/2024	8
BA2DD2F3-84DF-4FAF-BC43-5FE37DAA2197	2/18/2025	282
A0368570-D9E0-4DC2-84F3-4B33730206B3	2/19/2025	187
3DDE9D3F-63CF-4ABB-9096-8709FCE8F20C	7/12/2024	1114
21D4D912-E12E-4566-A5F2-E524F5176E05	6/2/2024	269
3B741CB0-C410-4262-BD04-62E9076CE548	7/7/2024	1354
5B89722B-B50D-47C8-AC40-300678BD8A57	10/24/2024	176
95966042-A24D-4CAA-BD53-CAC6C42624CE	12/3/2024	54
AE56920F-5886-EF11-AC21-6045BD01A9FD	3/27/2025	170
453A86E6-2DAC-41E9-95D0-B33F727A1748	5/16/2024	798
D87E48B5-EBCC-ED11-B597-00224809C677	1/18/2024	298
AF885A3E-A613-44DF-8BB2-F8551012C266	6/11/2024	884
513776B2-EBCC-ED11-B597-00224809CBC2	6/5/2024	437
085F8AA3-8BC9-45CF-8729-0581CC1F8BB7	11/13/2024	234
62CCDA46-C380-EA11-811B-0050569122EF	11/26/2024	1685
E7D766EC-3C1F-46AB-B5C6-31CAB3FEFEE4	6/17/2024	380
D3C291B7-EBCC-ED11-B597-00224809C243	7/8/2024	470

[Ashish_Excel · Accepted solution]

Hi,

Download the PBI file from here.

Hope this helps.

[Winter_king94]

Thank you! Really appreciate all your help!

[Ashish_Excel]

You are welcome.