cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
andrehawari Helper II
Helper II

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 III
Super User III

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] )
Try my new Power BI game Cross the River

View solution in original post

3 REPLIES 3
Super User III
Super User III

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] )
Try my new Power BI game Cross the River

View solution in original post

Super User III
Super User III

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

@andrehawari

 

See attached file as well

Try my new Power BI game Cross the River
v-piga-msft Resident Rockstar
Resident Rockstar

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
Announcing the New Spanish Forum

Announcing the New Spanish Forum

Do you need help in Spanish? Check out our new Spanish community section.

Top Solution Authors
Top Kudoed Authors