cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
andrehawari Regular Visitor
Regular Visitor

Calculate Moving Average Last 6 Month with not sequencial Date

Hi,  I need to create measure that calculate moving average for the last 6 months, but the date is not always sequencial 

 

for instance, In the Table below

Scenario 1: If I choose Jun 2018, the measure will display value average last 6 months, but with calculate from column that value is "Has Sales=Yes". That is (Oct 2017 + Nov 2017 + Dec 2017 + Jan 2018 + Mar 2018 + May 2018) / 6 = (19+20+21+22+24+26)/6

 

Scenario 2: If I choose March 2018, the measure will display value average last 6 months, but with calculate from column that value is "Has Sales=Yes". That is (Jul 2017 + Sep 2017 + Oct 2017 + Nov 2017 + Dec 2017+ Jan 2018) / 6 = (16+18+19+20+21+22)/6

 

 

MonthHas SalesValue
Jan-17Yes10
Feb-17Yes11
Mar-17No12
Apr-17No13
May-17Yes14
Jun-17Yes15
Jul-17Yes16
Aug-17No17
Sep-17Yes18
Oct-17Yes19
Nov-17Yes20
Dec-17Yes21
Jan-18Yes22
Feb-18No23
Mar-18Yes24
Apr-18No25
May-18Yes26
Jun-18Yes27

 


I found the solution in this link

https://community.powerbi.com/t5/Desktop/Average-of-last-6-months/td-p/128738

 

But This wont work in my case because the Datesinperiod function in above links only give statics last N months

Any idea would be really appreciated

 

Thanks !

1 ACCEPTED SOLUTION

Accepted Solutions
Super User
Super User

Re: Calculate Moving Average Last 6 Month with not sequencial Date

@andrehawari

 

Try this MEAASURE

 

Measure =
VAR mydate =
    SELECTEDVALUE ( Table1[Month] )
VAR Previous6 =
    TOPN (
        6,
        FILTER ( ALL ( Table1 ), Table1[Month] < mydate && Table1[Has Sales] = "Yes" ),
        [Month], DESC
    )
RETURN
    AVERAGEX ( Previous6, [Value] )

View solution in original post

3 REPLIES 3
Super User
Super User

Re: Calculate Moving Average Last 6 Month with not sequencial Date

@andrehawari

 

Try this MEAASURE

 

Measure =
VAR mydate =
    SELECTEDVALUE ( Table1[Month] )
VAR Previous6 =
    TOPN (
        6,
        FILTER ( ALL ( Table1 ), Table1[Month] < mydate && Table1[Has Sales] = "Yes" ),
        [Month], DESC
    )
RETURN
    AVERAGEX ( Previous6, [Value] )

View solution in original post

Super User
Super User

Re: Calculate Moving Average Last 6 Month with not sequencial Date

@andrehawari

 

See attached file as well

Community Support Team
Community Support Team

Re: Calculate Moving Average Last 6 Month with not sequencial Date

Hi @andrehawari,

 

By my test, the measure from @Zubair_Muhammad should be the solution.

 

If you have solved your problem, please accept the replies making sense as solution to your question so that people who may have the same question can get the solution directly.

 

If you still need help, please feel free to ask.

 

Best  Regards,

Cherry

Community Support Team _ Cherry Gao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Helpful resources

Announcements
Can You Solve These Challenge

Challenge: Can You Solve These?

Find out how to participate in the first Power BI 'Can You Solve These?' challenge.

Community News & Announcements

Community News & Announcements

Get your latest community news and announcements.

Virtual Launch Event

Microsoft Business Applications October Virtual Launch Event

Join us for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

Community Kudopalooza

Win Power BI Swag with Community Kudopalooza!

Each week, complete activities and be qualified in the drawing for cool Power BI Swag.

Users Online
Currently online: 59 members 730 guests
Please welcome our newest community members: