cancel
Showing results for 
Search instead for 
Did you mean: 

Fabric is Generally Available. Browse Fabric Presentations. Work towards your Fabric certification with the Cloud Skills Challenge.

Reply
sonya7
Helper III
Helper III

12 month rolling average

I have Amount data based on Startdate date. How can I write the 12-month average?

i'm new, it is important to me that you are clear 🙏 thanks

1 ACCEPTED SOLUTION

Hi,

You may download my PBI file from here.

Hope this helps.

Untitled.png


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

View solution in original post

16 REPLIES 16
Ashish_Mathur
Super User
Super User

Hi,

Share some data and show the expected result.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

Hello, I want the following rolling average 4 result. But since this measure was created with quick measure, I could only use it with hierarchy.
I want this measure to be used without a date hierarchy.

 

 

 

 

 

Hi,

Share the download link of the PBI file.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

**

Hi,

You may download my PBI file from here.

Hope this helps.

Untitled.png


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

thanks! that works. 

Can we show the year 2022 when it is selected as a cumulative? I also want to show the last 6 months @Ashish_Mathur 

You are welcome.  In my measure, change the -11 to -5.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

Can we show the year 2022 when it is selected as a cumulative? I also want to show the last 6 months @Ashish_Mathur 

Your question is not clear.  Please clarify.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

hi ashish,

i want to see your 12 month average cumulative every year. I also want to show the last 6 months while doing this

I still do not understand your requirement.  Show the expected result very clearly in an MS Excel file with formulas.  I will try to convert those Excel formulas into DAX formulas.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

*

Hi,

Someone else will help you.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
v-yiruan-msft
Community Support
Community Support

Hi @sonya7 ,

You can refer the following links to get it:

Rolling 12 Months Average in DAX

Sales R12M =
VAR NumOfMonths = 12
VAR LastCurrentDate =
    MAX ( 'Date'[Date] )
VAR Period =
    DATESINPERIOD ( 'Date'[Date], LastCurrentDate, - NumOfMonths, MONTH )
VAR Result =
    CALCULATE (
        AVERAGEX (
            VALUES ( 'Date'[Calendar Year Month] ),
            [Sales Amount]
        ),
        Period
    )
VAR FirstDateInPeriod = MINX ( Period, 'Date'[Date] )
VAR LastDateWithSales = MAX ( Sales[Order Date] )
RETURN
    IF ( FirstDateInPeriod <= LastDateWithSales, Result )
Community Support Team _ Rena
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Hi, to the extent that I use it for averaging, I unfortunately get incorrect results. Could you change the dimension in the image to its non-hierarchy?

sonya7_0-1669301241997.png

 

 

I don't understand what to do with it either. need help

legalfiles.com

Helpful resources

Announcements
PBI November 2023 Update Carousel

Power BI Monthly Update - November 2023

Check out the November 2023 Power BI update to learn about new features.

Community News

Fabric Community News unified experience

Read the latest Fabric Community announcements, including updates on Power BI, Synapse, Data Factory and Data Activator.

Power BI Fabric Summit Carousel

The largest Power BI and Fabric virtual conference

130+ sessions, 130+ speakers, Product managers, MVPs, and experts. All about Power BI and Fabric. Attend online or watch the recordings.

Top Solution Authors
Top Kudoed Authors