Moving average for non-consecutive dates.

Question

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 · Answer

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.&nbsp; It is just the MA7 of the Size column (no extra math beyond that).&nbsp; Please reply if you figure out the difference in what we are doing.&nbsp;If this works for you, please mark it as the solution.&nbsp; Kudos are appreciated too.&nbsp; Please let me know if not.Regards,Pat

amitchandak · Answer

Anonymous , create a dense Rank on the date inside the country and try a formula like this 
&nbsp;
Like
Last 7 Dates = CALCULATE(SUM(Sales[Net Sales]),FILTER(allselected('Table'),'Table'[Rank]&gt;=min('Table'[Rank])-7 &amp;&amp; 'Table'[Rank]&lt;=max('Table'[Rank])))
or
&nbsp;
Last 7 Dates = CALCULATE(SUM(Sales[Net Sales]),FILTER(allselected('Table'),'Table'[Rank]&gt;=min('Table'[Rank])-7 &amp;&amp; 'Table'[Rank]&lt;=max('Table'[Rank]) &amp;&amp;&nbsp;&amp;&amp; 'Table'[Country]=max('Table'[Country]) ))
&nbsp;
&nbsp;
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 · Answer

Hi&nbsp;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?&nbsp;Thanks!

mahoneypat · Answer

Please try this measure expression in a table or matrix visual with the Date and Country columns in it.&nbsp;MA7 =
VAR thisdate =
    MIN ( Test[Date] )
RETURN
    CALCULATE (
        AVERAGE ( Test[Size] ),
        ALL ( Test[Date] ),
        TOPN (
            7,
            FILTER ( ALL ( Test[Date] ), Test[Date] &lt;= thisdate ),
            Test[Date], DESC
        )
    )&nbsp;If this works for you, please mark it as the solution.&nbsp; Kudos are appreciated too.&nbsp; Please let me know if not.Regards,Pat

amitchandak · Answer

Anonymous&nbsp;, for Rank refer. use category/sub category rank&nbsp;
For Rank Refer these linkshttps://radacad.com/how-to-use-rankx-in-dax-part-2-of-3-calculated-measureshttps://radacad.com/how-to-use-rankx-in-dax-part-1-of-3-calculated-columnshttps://radacad.com/how-to-use-rankx-in-dax-part-3-of-3-the-finalehttps://community.powerbi.com/t5/Community-Blog/Dynamic-TopN-made-easy-with-What-If-Parameter/ba-p/367415

Forum Discussion

Moving average for non-consecutive dates.

9 Replies

Related content

moving average help

Average and Moving Average

Moving Average

Moving Average

Moving average