Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Rolling average on index

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
 
A21+12/13-14 (not 2/15 because its a weekend)
B33232 + 72 + 58 + 82 + 882/11-14, 2/17
C5011 + 7 +10 + 10 + 13 + 92/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  
Need Help
  • mahoneypat's avatar
    mahoneypat
    6 years ago

    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

1 Reply

  • mahoneypat's avatar
    mahoneypat
    Icon for Microsoft Employee rankMicrosoft Employee
    6 years ago

    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