topic Re: Last 12 Months not affected by date filter in DAX Commands and Tips

Last 12 Months not affected by date filter

BA11501Banderso — Tue, 03 May 2022 11:27:15 GMT

Hello, I'm trying to get a rolling 12 month average. I need to first sum the month and then average the 12 months from there. For example, the average for the table below should be $121,955,757.71. Instead I keep getting $60,977,878.86 (the average of all the numbers together). I can't seem to figure it out. Thank you!

Re: Last 12 Months not affected by date filter

BeaBF — Tue, 03 May 2022 12:09:10 GMT

BA11501Banderso Hi!

Can you paste your data instead of the picture?

Thx,

Re: Last 12 Months not affected by date filter

BA11501Banderso — Tue, 03 May 2022 12:40:41 GMT

Account	Asset Item	Amount	Date
100	Widget1	44,781.48	2/28/2021
100	Widget1	44,781.48	1/31/2021
100	Widget1	44,781.48	4/30/2021
100	Widget1	44,781.48	3/31/2021
100	Widget1	44,781.48	6/30/2021
100	Widget1	44,781.48	5/31/2021
100	Widget1	44,781.48	8/31/2021
100	Widget1	44,781.48	7/31/2021
100	Widget1	44,781.48	10/31/2021
100	Widget1	44,781.48	9/30/2021
100	Widget1	44,781.48	11/30/2021
100	Widget1	44,781.48	12/31/2021
200	Widget2	121,606,727.55	2/28/2021
200	Widget2	121,606,634.10	1/31/2021
200	Widget2	121,600,443.75	4/30/2021
200	Widget2	121,588,370.18	3/31/2021
200	Widget2	121,602,452.29	6/30/2021
200	Widget2	121,600,498.67	5/31/2021
200	Widget2	122,132,385.94	8/31/2021
200	Widget2	122,122,859.25	7/31/2021
200	Widget2	122,166,275.57	10/31/2021
200	Widget2	122,167,506.02	9/30/2021
200	Widget2	122,357,357.21	11/30/2021
200	Widget2	122,380,204.26	12/31/2021

Here it is. Thank you!

Re: Last 12 Months not affected by date filter

BeaBF — Tue, 03 May 2022 12:59:06 GMT

BA11501Banderso

First calculate these two columns:

- Month = FORMAT('Table'[Date],"MMMM")

- Year = YEAR('Table'[Date])

Then, use this measure to obtain your result:

AVG =
VAR countmonth = CALCULATE(DISTINCTCOUNT('Table'[Month]), ALLEXCEPT('Table', 'Table'[Year]))
RETURN CALCULATE(SUM('Table'[ Amount ]), ALLEXCEPT('Table', 'Table'[Year])) / countmonth

Re: Last 12 Months not affected by date filter

BA11501Banderso — Tue, 03 May 2022 16:35:48 GMT

So I'm looking for a rolling average. This seems to just populates the total number into each month. I guess it would be better is I gave you more than 12 months of data...

Account	Asset Item	Amount	Date
100	Widget1	44,781.48	2/28/2021
100	Widget1	44,781.48	1/31/2021
100	Widget1	44,781.48	4/30/2021
100	Widget1	44,781.48	3/31/2021
100	Widget1	44,781.48	6/30/2021
100	Widget1	44,781.48	5/31/2021
100	Widget1	44,781.48	8/31/2021
100	Widget1	44,781.48	7/31/2021
100	Widget1	44,781.48	10/31/2021
100	Widget1	44,781.48	9/30/2021
100	Widget1	44,781.48	11/30/2021
100	Widget1	44,781.48	12/31/2021
200	Widget2	121,606,727.55	2/28/2021
200	Widget2	121,606,634.10	1/31/2021
200	Widget2	121,600,443.75	4/30/2021
200	Widget2	121,588,370.18	3/31/2021
200	Widget2	121,602,452.29	6/30/2021
200	Widget2	121,600,498.67	5/31/2021
200	Widget2	122,132,385.94	8/31/2021
200	Widget2	122,122,859.25	7/31/2021
200	Widget2	122,166,275.57	10/31/2021
200	Widget2	122,167,506.02	9/30/2021
200	Widget2	122,357,357.21	11/30/2021
200	Widget2	122,380,204.26	12/31/2021
100	Widget1	44,781.48	2/28/2022
100	Widget1	44,781.48	1/31/2022
100	Widget1	44,781.48	4/30/2022
100	Widget1	44,781.48	3/31/2022
100	Widget1	44,781.48	6/30/2022
100	Widget1	44,781.48	5/31/2022
100	Widget1	44,781.48	8/31/2022
100	Widget1	44,781.48	7/31/2022
100	Widget1	44,781.48	10/31/2022
100	Widget1	44,781.48	9/30/2022
100	Widget1	44,781.48	11/30/2022
100	Widget1	44,781.48	12/31/2022
200	Widget2	121,606,727.55	2/28/2022
200	Widget2	121,606,634.10	1/31/2022
200	Widget2	121,600,443.75	4/30/2022
200	Widget2	121,588,370.18	3/31/2022
200	Widget2	121,602,452.29	6/30/2022
200	Widget2	121,600,498.67	5/31/2022
200	Widget2	122,132,385.94	8/31/2022
200	Widget2	122,122,859.25	7/31/2022
200	Widget2	122,166,275.57	10/31/2022
200	Widget2	122,167,506.02	9/30/2022
200	Widget2	122,357,357.21	11/30/2022
200	Widget2	122,380,204.26	12/31/2022

Re: Last 12 Months not affected by date filter

BeaBF — Tue, 03 May 2022 16:48:58 GMT

BA11501Banderso Sorry, a mistake in the measure, set Month with Year in the return, like this:

AVG =
VAR countmonth = CALCULATE(DISTINCTCOUNT('Table'[Month]), ALLEXCEPT('Table', 'Table'[Year]))
RETURN CALCULATE(SUM('Table'[ Amount ]), ALLEXCEPT('Table', 'Table'[Month], 'Table'[Year])) / countmonth

Re: Last 12 Months not affected by date filter

BA11501Banderso — Tue, 03 May 2022 16:49:53 GMT

That doesn't appear to be giving me what I'm looking for. That appears to give me the total for the month divided by 12.

I'm looking to sum the months individually (multiple inputs into each month) and then take an average of the total of the prior 12 months.

Re: Last 12 Months not affected by date filter

BeaBF — Tue, 03 May 2022 16:58:36 GMT

BA11501Banderso can you paste a table with the expected result? To better understand

Thx

Re: Last 12 Months not affected by date filter

BA11501Banderso — Tue, 03 May 2022 17:07:12 GMT

Yup, I'd like it to look something like this...

Date	Actual Monthly Amount	Last Twelve Months
1/1/2021	121,651,415.58	121,651,415.58
2/1/2021	121,651,509.03	121,651,462.31
3/1/2021	121,633,151.66	121,645,358.76
4/1/2021	121,645,225.23	121,645,325.38
5/1/2021	121,645,280.15	121,645,316.33
6/1/2021	121,647,233.77	121,645,635.90
7/1/2021	122,167,640.73	121,720,208.02
8/1/2021	122,177,167.42	121,777,327.95
9/1/2021	122,212,287.50	121,825,656.79
10/1/2021	122,211,057.05	121,864,196.81
11/1/2021	122,402,138.69	121,913,100.62
12/1/2021	122,424,985.74	121,955,757.71
1/1/2022	121,609,134.10	121,952,234.26
2/1/2022	121,651,509.03	121,952,234.26
3/1/2022	121,592,170.18	121,948,819.13
4/1/2022	121,645,225.23	121,948,819.13
5/1/2022	121,645,498.67	121,948,837.34
6/1/2022	121,445,447.48	121,932,021.82
7/1/2022	122,167,640.73	121,932,021.82
8/1/2022	122,184,385.94	121,932,623.36
9/1/2022	122,178,506.02	121,929,808.24
10/1/2022	118,045,381.48	121,582,668.61
11/1/2022	122,402,138.69	121,582,668.61
12/1/2022	122,424,985.74	121,582,668.61

24 months of data

Asset Item	Amount	Date
Widget1	44,781.48	2/28/2021
Widget1	44,781.48	1/31/2021
Widget1	44,781.48	4/30/2021
Widget1	44,781.48	3/31/2021
Widget1	44,781.48	6/30/2021
Widget1	44,781.48	5/31/2021
Widget1	44,781.48	8/31/2021
Widget1	44,781.48	7/31/2021
Widget1	44,781.48	10/31/2021
Widget1	44,781.48	9/30/2021
Widget1	44,781.48	11/30/2021
Widget1	44,781.48	12/31/2021
Widget2	121,606,727.55	2/28/2021
Widget2	121,606,634.10	1/31/2021
Widget2	121,600,443.75	4/30/2021
Widget2	121,588,370.18	3/31/2021
Widget2	121,602,452.29	6/30/2021
Widget2	121,600,498.67	5/31/2021
Widget2	122,132,385.94	8/31/2021
Widget2	122,122,859.25	7/31/2021
Widget2	122,166,275.57	10/31/2021
Widget2	122,167,506.02	9/30/2021
Widget2	122,357,357.21	11/30/2021
Widget2	122,380,204.26	12/31/2021
Widget1	44,781.48	2/28/2022
Widget1	2,500.00	1/31/2022
Widget1	44,781.48	4/30/2022
Widget1	3,800.00	3/31/2022
Widget1	44,781.48	6/30/2022
Widget1	45,000.00	5/31/2022
Widget1	52,000.00	8/31/2022
Widget1	44,781.48	7/31/2022
Widget1	44,781.48	10/31/2022
Widget1	11,000.00	9/30/2022
Widget1	44,781.48	11/30/2022
Widget1	44,781.48	12/31/2022
Widget2	121,606,727.55	2/28/2022
Widget2	121,606,634.10	1/31/2022
Widget2	121,600,443.75	4/30/2022
Widget2	121,588,370.18	3/31/2022
Widget2	121,400,666.00	6/30/2022
Widget2	121,600,498.67	5/31/2022
Widget2	122,132,385.94	8/31/2022
Widget2	122,122,859.25	7/31/2022
Widget2	118,000,600.00	10/31/2022
Widget2	122,167,506.02	9/30/2022
Widget2	122,357,357.21	11/30/2022
Widget2	122,380,204.26	12/31/2022

Thank you!

Re: Last 12 Months not affected by date filter

BeaBF — Tue, 03 May 2022 17:42:52 GMT

BA11501Banderso

With this measure i achieve the correct result for 2021, but i didn't understand what it have to do with the new year (2022), can you explain?

In this row:

1/1/2022

121,609,134.10

121,952,234.26

How to you calculate the expected value?

The measure:

Cumulative_Actual =
VAR countmonths = CALCULATE (
DISTINCTCOUNT( 'Table'[Month]),
filter( ALL ( 'Table'[Date]),
'Table'[Date] <= MAX( 'Table'[Date] ))
)
RETURN CALCULATE (
SUM ('Table'[ Amount ] ),
filter( ALL ( 'Table'[Date]),
'Table'[Date] <= MAX( 'Table'[Date] ))
) / countmonths

Re: Last 12 Months not affected by date filter

v-chenwuz-msft — Fri, 06 May 2022 07:04:16 GMT

Hi BA11501Banderso,

Please try this code:

Measurde = VAR _s = DATE ( SELECTEDVALUE ( 'Table'[Date].[Year] ), SELECTEDVALUE ( 'Table'[Date].[MonthNo] ) - 11, 1 ) VAR _e = DATE ( SELECTEDVALUE ( 'Table'[Date].[Year] ), SELECTEDVALUE ( 'Table'[Date].[MonthNo] ) + 1, 1 ) - 1 RETURN AVERAGEX ( TOPN ( 12, SUMMARIZE ( FILTER ( ALL ( 'Table' ), [Date] >= _s && [Date] <= _e ), [Date], "sum", SUM ( 'Table'[Amount] ) ), [Date], DESC ), [sum] )

Result:

Pbix in the end you can refer.

Best Regards

Community Support Team _ chenwu zhu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Re: Last 12 Months not affected by date filter

BA11501Banderso — Fri, 06 May 2022 11:23:10 GMT

This is AWESOME! Thank you!!