Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Reply
twister8889
Helper V
Helper V

Moving average by month / moving variance

Hi guys,

 

I need to calculate the average considering that: The previous months of 12, has average by the quantity of month. So if I have 6 months, the average is replicated to the previous months, if I have 7,8,9,10,11 and 12 months I have the average replicated to the previous months. If it has 13 months, I need to calculate the moving average by 12 months (this is working).

 

So, considering the image, I need that AVG2 has the value 0.366333333 to index minors that 13.

 

moving avg.png

1 ACCEPTED SOLUTION

@twister8889 

Use this measure :

New Avg = 
VAR IND = SELECTEDVALUE('Table'[Index])
VAR _12 = 
    CALCULATE(
        AVERAGE('Table'[Value]),
        'Table'[Index] <= 12,
        ALLSELECTED('Table')
)

VAR _OVER12 = 
    CALCULATE(
        AVERAGE('Table'[Value]),
        'Table'[Index] <= IND && 'Table'[Index] > IND - 12  
        ,ALLSELECTED('Table')
    )

RETURN

IF( IND > 12,
    _OVER12,
    _12
)

 

________________________

Did I answer your question? Mark this post as a solution, this will help others!.

Click on the Thumbs-Up icon on the right if you like this reply 🙂

YouTube, LinkedIn

Did I answer your question? Mark my post as a solution! and hit thumbs up


Subscribe and learn Power BI from these videos

Website LinkedIn PBI User Group

View solution in original post

7 REPLIES 7
Fowmy
Super User
Super User

@twister8889 

Can you share some sample data in excel/csv format and the expected result to have a clear understanding of your question?
You can save your files in OneDrive, Google Drive, or any other cloud sharing platforms and share the link here.
____________________________________
How to paste sample data with your question?
How to get your questions answered quickly?

_____________________________________
Did I answer your question? Mark this post as a solution, this will help others!.

Click on the Thumbs-Up icon on the right if you like this reply 🙂

YouTube, LinkedIn

Did I answer your question? Mark my post as a solution! and hit thumbs up


Subscribe and learn Power BI from these videos

Website LinkedIn PBI User Group

First of all, thank you for your answer, I share my pbi (Page 2) file here: https://1drv.ms/u/s!AoDYwrtLrltJnxlh-8VaZdW7EZVg

@twister8889 

Your explanation is no quite clear for me, can you share the expected result for each line, maybe on Excel?

________________________

Did I answer your question? Mark this post as a solution, this will help others!.

Click on the Thumbs-Up icon on the right if you like this reply 🙂

YouTube, LinkedIn

Did I answer your question? Mark my post as a solution! and hit thumbs up


Subscribe and learn Power BI from these videos

Website LinkedIn PBI User Group

OK, please check is more clear for you..

https://1drv.ms/x/s!AoDYwrtLrltJn0KJ4HE5amJyVpK2?e=Xd02Mz

 

HI @twister8889 ,

 

You will need to create an index column.

You can create this in Power Query 

 

Post this you can use this measure.

 

Moving_Average_12_Months =
IF (
    MAX ( Table1[Index] ) > 12,
    AVERAGEX (
        DATESINPERIOD (
            Table1[Date],
            LASTDATE ( 'Table1'[Date] ),
            -12,
            MONTH
        ),
        CALCULATE (
            SUM ( Table1[Values] )
        )
    ),
    DIVIDE (
        CALCULATE (
            SUM ( Table1[Values] ),
            FILTER (
                ALL ( Table1 ),
                Table1[Index] <= 12
            )
        ),
        12
    )
)

 

 

1.jpg

 

Regards,
Harsh Nathani

Appreciate with a Kudos!! (Click the Thumbs Up Button)
Did I answer your question? Mark my post as a solution!

@twister8889 

Use this measure :

New Avg = 
VAR IND = SELECTEDVALUE('Table'[Index])
VAR _12 = 
    CALCULATE(
        AVERAGE('Table'[Value]),
        'Table'[Index] <= 12,
        ALLSELECTED('Table')
)

VAR _OVER12 = 
    CALCULATE(
        AVERAGE('Table'[Value]),
        'Table'[Index] <= IND && 'Table'[Index] > IND - 12  
        ,ALLSELECTED('Table')
    )

RETURN

IF( IND > 12,
    _OVER12,
    _12
)

 

________________________

Did I answer your question? Mark this post as a solution, this will help others!.

Click on the Thumbs-Up icon on the right if you like this reply 🙂

YouTube, LinkedIn

Did I answer your question? Mark my post as a solution! and hit thumbs up


Subscribe and learn Power BI from these videos

Website LinkedIn PBI User Group

I will test, but I think that it is the correct solution, thank you so much.

 

Let me try to test this solution until Monday to check as a solution ok? More one time, thank you so much.

Helpful resources

Announcements
LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

April Fabric Community Update

Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.