Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Reply
738o51
Helper II
Helper II

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  
1 ACCEPTED SOLUTION
mahoneypat
Employee
Employee

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





Did I answer your question? Mark my post as a solution! Kudos are also appreciated!

To learn more about Power BI, follow me on Twitter or subscribe on YouTube.


@mahoneypa HoosierBI on YouTube


View solution in original post

1 REPLY 1
mahoneypat
Employee
Employee

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





Did I answer your question? Mark my post as a solution! Kudos are also appreciated!

To learn more about Power BI, follow me on Twitter or subscribe on YouTube.


@mahoneypa HoosierBI on YouTube


Helpful resources

Announcements
LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

April Fabric Community Update

Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.