12 month rolling average

[sonya7]

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

[Ashish_Mathur]

Hi,

Share some data and show the expected result.

[sonya7]

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.

[Ashish_Mathur]

Hi,

Share the download link of the PBI file.

[sonya7]

**

[Ashish_Mathur · Accepted solution]

Hi,

You may download my PBI file from here.

Hope this helps.

[sonya7]

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 

[Ashish_Mathur]

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

[sonya7]

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

[Ashish_Mathur]

Your question is not clear.  Please clarify.

[sonya7]

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

[Ashish_Mathur]

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.

[sonya7]

*

[Ashish_Mathur]

Hi,

Someone else will help you.

[v-yiruan-msft]

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 )

Rolling 12 months average

Rolling 12 Months Visuals

Best Regards

[sonya7]

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?

[maryyyleg]

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

legalfiles.com