Moving average for non-consecutive dates.

[Anonymous]

I understand that there have been some articles regarding this issue, however, none of them seem to work for me. Would like some help specifically to my situation/data.

Basically I have a table with a few columns. Date, country, size. I would like to calculate the 7 day moving average for a country's size
(E.g. Libya's size). However, Libya does not have size data for all the dates, as shown in the picture below. The moving average formula I used, as shown in the other picture, includes the days where Libya does not have data. What formula should I be using if I want the moving average to consist of 7 non-zero days?

Thanks for the help.

[mahoneypat]

Please try this measure expression in a table or matrix visual with the Date and Country columns in it.

MA7 =
VAR thisdate =
    MIN ( Test[Date] )
RETURN
    CALCULATE (
        AVERAGE ( Test[Size] ),
        ALL ( Test[Date] ),
        TOPN (
            7,
            FILTER ( ALL ( Test[Date] ), Test[Date] <= thisdate ),
            Test[Date], DESC
        )
    )

If this works for you, please mark it as the solution.  Kudos are appreciated too.  Please let me know if not.

Regards,

Pat

[Anonymous]

Hi @mahoneypat ,

It does not seem to work. Below are the results in PowerBI (Date, Country, Size, MA7), vs. what I calculated manually via Excel.

Thanks!

[amitchandak]

@Anonymous , Try @mahoneypat solution. If it does not work out.

Can you share sample data and sample output in table format? 

[mahoneypat]

I put in your sample data and tried that measure out, and got the results below that match with what I calculate in Excel too.  It is just the MA7 of the Size column (no extra math beyond that).  Please reply if you figure out the difference in what we are doing.

If this works for you, please mark it as the solution.  Kudos are appreciated too.  Please let me know if not.

Regards,

Pat

[Anonymous]

Hi @mahoneypat ,

Yeah yours looks great. Not sure why it doesnt work on mine, maybe because of the different filters on size? I'll let you know if i figure it out thanks!

[Anonymous]

Hi @amitchandak ,

I have done @mahoneypat 's method, and have the results in the screenshot as above. Unfortunately, still not getting the correct results, thanks!

[amitchandak]

@Anonymous , create a dense Rank on the date inside the country and try a formula like this

Like

Last 7 Dates = CALCULATE(SUM(Sales[Net Sales]),FILTER(allselected('Table'),'Table'[Rank]>=min('Table'[Rank])-7
&& 'Table'[Rank]<=max('Table'[Rank])))

or

Last 7 Dates = CALCULATE(SUM(Sales[Net Sales]),FILTER(allselected('Table'),'Table'[Rank]>=min('Table'[Rank])-7
&& 'Table'[Rank]<=max('Table'[Rank]) && && 'Table'[Country]=max('Table'[Country]) ))

https://community.powerbi.com/t5/Community-Blog/Any-Weekday-Week-Decoding-Date-and-Calendar-2-5-Power-BI-Turning/ba-p/1187482

https://community.powerbi.com/t5/Community-Blog/Week-Is-Not-So-Weak-WTD-Last-WTD-and-This-Week-vs-Last-Week/ba-p/1051123

[Anonymous]

Hi @amitchandak,
To create a rank on the date inside the country, how should I do it?
Do i create a conditional column for the dates and size when country = "Libya", then index that column? 
Thanks!

[amitchandak]

@Anonymous , for Rank refer. use category/sub category rank 

For Rank Refer these links
https://radacad.com/how-to-use-rankx-in-dax-part-2-of-3-calculated-measures
https://radacad.com/how-to-use-rankx-in-dax-part-1-of-3-calculated-columns
https://radacad.com/how-to-use-rankx-in-dax-part-3-of-3-the-finale
https://community.powerbi.com/t5/Community-Blog/Dynamic-TopN-made-easy-with-What-If-Parameter/ba-p/367415