Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now
Hi everyone,
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 |
Solved! Go to 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
To learn more about Power BI, follow me on Twitter or subscribe on YouTube.
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
To learn more about Power BI, follow me on Twitter or subscribe on YouTube.
Check out the October 2024 Power BI update to learn about new features.
Learn from experts, get hands-on experience, and win awesome prizes.
User | Count |
---|---|
107 | |
96 | |
92 | |
87 | |
69 |
User | Count |
---|---|
165 | |
130 | |
129 | |
102 | |
98 |