Skip to main content
cancel
Turn on suggestions
Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.
Showing results for 
Search instead for 
Did you mean: 

A new Data Days event is coming soon! This time we’re going bigger than ever. Fabric, Power BI, SQL, AI and more. Don't miss out.

Reply
BA11501Banderso
Frequent Visitor

Last 12 Months not affected by date filter

‎05-03-2022 04:27 AM

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!

 

BA11501Banderso_0-1651577210764.png

 

Message 1 of 12
1,912 Views
0
1 ACCEPTED SOLUTION
v-chenwuz-msft
Community Support
Community Support
In response to BA11501Banderso

‎05-06-2022 12:04 AM

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:

vchenwuzmsft_0-1651820613265.png

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.

 

View solution in original post

Last 12 Months not affected by date filter.pbix
Message 11 of 12
1,741 Views
11 REPLIES 11
BA11501Banderso
Frequent Visitor

‎05-03-2022 05:40 AM
AccountAsset ItemAmountDate
100Widget144,781.482/28/2021
100Widget144,781.481/31/2021
100Widget144,781.484/30/2021
100Widget144,781.483/31/2021
100Widget144,781.486/30/2021
100Widget144,781.485/31/2021
100Widget144,781.488/31/2021
100Widget144,781.487/31/2021
100Widget144,781.4810/31/2021
100Widget144,781.489/30/2021
100Widget144,781.4811/30/2021
100Widget144,781.4812/31/2021
200Widget2121,606,727.552/28/2021
200Widget2121,606,634.101/31/2021
200Widget2121,600,443.754/30/2021
200Widget2121,588,370.183/31/2021
200Widget2121,602,452.296/30/2021
200Widget2121,600,498.675/31/2021
200Widget2122,132,385.948/31/2021
200Widget2122,122,859.257/31/2021
200Widget2122,166,275.5710/31/2021
200Widget2122,167,506.029/30/2021
200Widget2122,357,357.2111/30/2021
200Widget2122,380,204.2612/31/2021

 

Here it is.  Thank you!

Message 3 of 12
1,866 Views
0
BeaBF
Super User
Super User
In response to BA11501Banderso

‎05-03-2022 05:59 AM

@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

 

BF

Message 4 of 12
1,862 Views
0
BA11501Banderso
Frequent Visitor
In response to BeaBF

‎05-03-2022 09:35 AM

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...

 

BA11501Banderso_0-1651595726921.png

 

 

AccountAsset ItemAmountDate
100Widget144,781.482/28/2021
100Widget144,781.481/31/2021
100Widget144,781.484/30/2021
100Widget144,781.483/31/2021
100Widget144,781.486/30/2021
100Widget144,781.485/31/2021
100Widget144,781.488/31/2021
100Widget144,781.487/31/2021
100Widget144,781.4810/31/2021
100Widget144,781.489/30/2021
100Widget144,781.4811/30/2021
100Widget144,781.4812/31/2021
200Widget2121,606,727.552/28/2021
200Widget2121,606,634.101/31/2021
200Widget2121,600,443.754/30/2021
200Widget2121,588,370.183/31/2021
200Widget2121,602,452.296/30/2021
200Widget2121,600,498.675/31/2021
200Widget2122,132,385.948/31/2021
200Widget2122,122,859.257/31/2021
200Widget2122,166,275.5710/31/2021
200Widget2122,167,506.029/30/2021
200Widget2122,357,357.2111/30/2021
200Widget2122,380,204.2612/31/2021
100Widget144,781.482/28/2022
100Widget144,781.481/31/2022
100Widget144,781.484/30/2022
100Widget144,781.483/31/2022
100Widget144,781.486/30/2022
100Widget144,781.485/31/2022
100Widget144,781.488/31/2022
100Widget144,781.487/31/2022
100Widget144,781.4810/31/2022
100Widget144,781.489/30/2022
100Widget144,781.4811/30/2022
100Widget144,781.4812/31/2022
200Widget2121,606,727.552/28/2022
200Widget2121,606,634.101/31/2022
200Widget2121,600,443.754/30/2022
200Widget2121,588,370.183/31/2022
200Widget2121,602,452.296/30/2022
200Widget2121,600,498.675/31/2022
200Widget2122,132,385.948/31/2022
200Widget2122,122,859.257/31/2022
200Widget2122,166,275.5710/31/2022
200Widget2122,167,506.029/30/2022
200Widget2122,357,357.2111/30/2022
200Widget2122,380,204.2612/31/2022
Message 5 of 12
1,854 Views
0
BeaBF
Super User
Super User
In response to BA11501Banderso

‎05-03-2022 09:40 AM

@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

 

BF

Message 6 of 12
1,849 Views
0
BA11501Banderso
Frequent Visitor
In response to BeaBF

‎05-03-2022 09:49 AM

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. 

 

BA11501Banderso_0-1651596542696.png

 

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.

Message 7 of 12
1,846 Views
0
BeaBF
Super User
Super User
In response to BA11501Banderso

‎05-03-2022 09:58 AM

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

Thx

BF

Message 8 of 12
1,841 Views
0
BA11501Banderso
Frequent Visitor
In response to BeaBF

‎05-03-2022 10:07 AM

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

DateActual Monthly AmountLast 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 ItemAmountDate
Widget144,781.482/28/2021
Widget144,781.481/31/2021
Widget144,781.484/30/2021
Widget144,781.483/31/2021
Widget144,781.486/30/2021
Widget144,781.485/31/2021
Widget144,781.488/31/2021
Widget144,781.487/31/2021
Widget144,781.4810/31/2021
Widget144,781.489/30/2021
Widget144,781.4811/30/2021
Widget144,781.4812/31/2021
Widget2121,606,727.552/28/2021
Widget2121,606,634.101/31/2021
Widget2121,600,443.754/30/2021
Widget2121,588,370.183/31/2021
Widget2121,602,452.296/30/2021
Widget2121,600,498.675/31/2021
Widget2122,132,385.948/31/2021
Widget2122,122,859.257/31/2021
Widget2122,166,275.5710/31/2021
Widget2122,167,506.029/30/2021
Widget2122,357,357.2111/30/2021
Widget2122,380,204.2612/31/2021
Widget144,781.482/28/2022
Widget12,500.001/31/2022
Widget144,781.484/30/2022
Widget13,800.003/31/2022
Widget144,781.486/30/2022
Widget145,000.005/31/2022
Widget152,000.008/31/2022
Widget144,781.487/31/2022
Widget144,781.4810/31/2022
Widget111,000.009/30/2022
Widget144,781.4811/30/2022
Widget144,781.4812/31/2022
Widget2121,606,727.552/28/2022
Widget2121,606,634.101/31/2022
Widget2121,600,443.754/30/2022
Widget2121,588,370.183/31/2022
Widget2121,400,666.006/30/2022
Widget2121,600,498.675/31/2022
Widget2122,132,385.948/31/2022
Widget2122,122,859.257/31/2022
Widget2118,000,600.0010/31/2022
Widget2122,167,506.029/30/2022
Widget2122,357,357.2111/30/2022
Widget2122,380,204.2612/31/2022

 

Thank you!

Message 9 of 12
1,833 Views
0
v-chenwuz-msft
Community Support
Community Support
In response to BA11501Banderso

‎05-06-2022 12:04 AM

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:

vchenwuzmsft_0-1651820613265.png

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.

 

Last 12 Months not affected by date filter.pbix
Message 11 of 12
1,742 Views
BA11501Banderso
Frequent Visitor
In response to v-chenwuz-msft

‎05-06-2022 04:23 AM

This is AWESOME!  Thank you!!

Message 12 of 12
1,732 Views
0
BeaBF
Super User
Super User
In response to BA11501Banderso

‎05-03-2022 10:39 AM

@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

 

BF

Message 10 of 12
1,828 Views
0
BeaBF
Super User
Super User

‎05-03-2022 05:09 AM

@BA11501Banderso Hi!

 

Can you paste your data instead of the picture?

 

Thx,

BF

Message 2 of 12
1,872 Views
0

Helpful resources

Announcements
May Power BI Update Carousel

Power BI Monthly Update - May 2026

Check out the May 2026 Power BI update to learn about new features.

Fabric SQL PBI Data Days

Data Days 2026 coming soon!

Sign up to receive a private message when registration opens and key events begin.

New to Fabric survey Carousel

New to Fabric Survey

If you have recently started exploring Fabric, we'd love to hear how it's going. Your feedback can help with product improvements.

Power BI DataViz World Championships carousel

Power BI DataViz World Championships - June 2026

A new Power BI DataViz World Championship is coming this June! Don't miss out on submitting your entry.

View All