cancel
Showing results for
Did you mean:

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

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

 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
1 ACCEPTED SOLUTION
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!

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!

Announcements

#### Fabric certifications survey

Certification feedback opportunity for the community.

#### Power BI Monthly Update - April 2024

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

#### Fabric Community Update - April 2024

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

Top Solution Authors
Top Kudoed Authors