Rolling average on index

[Anonymous]

Hi everyone,

I need some help on creating a rolling average. My data show the number of customers who came into stores A, B, and C, by date. My rolling average should have the following:

1. Numerator: Count of customers from the last 5 open days; do not count customers who came on non-open days.

2. Denominator: Count of days that the store had customers in the last 5 open days. If I was open 5 days this week, but only had customers for 2, then my denominator would be 2.

Since I am only counting the open dates, I created an index to use for the rolling average rather than dates. Then I tried two meausres (Dax and PBIX below) that didn't work for the numerator. Can anyone help?

A correct meausre will show the following for 2/17/20

A	2	1+1	2/13-14 (not 2/15 because its a weekend)
B	332	32 + 72 + 58 + 82 + 88	2/11-14, 2/17
C	50	11 + 7 +10 + 10 + 13 + 9	2/11-14, 2/17

Here is the pbix

https://www.dropbox.com/s/pz3wm8cca8m5e2t/Retail%20example2.pbix?dl=0


Here is my Dax for the two measures:

Rolling5OpenDaySum1 = 

Calculate(

    sum(FootTraffic[Customer]),

    Topn(

        5,

        FootTraffic,

        FootTraffic[OpenDayIndex],

        ASC,1))

Rolling5OpenDaySum2 = 

Var LowerBound = max(FootTraffic[OpenDayIndex])

Var UpperBound = max(FootTraffic[OpenDayIndex])+5

Var RollingSum = 

Calculate(

    sum(FootTraffic[Customer]),

    filter(FootTraffic, FootTraffic[OpenDayIndex] >= LowerBound),

    filter (FootTraffic,FootTraffic[OpenDayIndex] < UpperBound))

Return

RollingSum  

[mahoneypat · Accepted solution]

Please try this expression.  It gets the correct result for Stores B and C on 2/17/2020.  However, since Store A had no customers on that day, it returns blank for Store A when the date = 2/17/2020.  To get around that, you'll need to add a Date table and make a relationship to your FootTraffic[Date] column.  Then you can adapt this measure to get the VALUES of your Date[Date] column instead.

Last 5 Open Days =
VAR __maxdate =
    MAX ( FootTraffic[Date] )
VAR __openlast5 =
    TOPN (
        5,
        CALCULATETABLE (
            VALUES ( FootTraffic[Date] ),
            FootTraffic[Open Day] = "Open",
            ALL ( FootTraffic[Date] ),
            FootTraffic[Date] >= __maxdate - 6,
            FootTraffic[Date] <= __maxdate
        ),
        FootTraffic[Date], DESC
    )
VAR __customercount =
    SUMX ( __openlast5, CALCULATE ( SUM ( FootTraffic[Customer] ) ) )
VAR __opendayswithcustomers =
    COUNTROWS (
        FILTER ( __openlast5, CALCULATE ( SUM ( FootTraffic[Customer] ) ) > 0 )
    )
RETURN
    DIVIDE ( __customercount, __opendayswithcustomers )

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

Regards,

Pat