Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hi Everyone - Could use some help with this problem that I cannot seem to solve:
The Challenge: Need to calculate weekly same store sales only on the days of the week the store was open.
Example: The winter storm last week in Texas is a perfect example. Here you see that Athens, TX was open Saturday, Sunday and Monday, and closed Tuesday through Friday due to weather:
The problem: The measure [Last Year Sales Same Store] is including [LY Sales] on the days that Athens is closed.
The desired result: The measure [Last Year Sales Same Store] should only include sales on the days Athens is open. In this case the desired result = $1,711 + $1,788 + $2,041 = $5,600.
Hoping someone smarter than me can get me started in the right direction. Thanks in advance. Measure definitions are below:
Same Store Sales Store Count = AVERAGEX( VALUES ('Day Key'[Business Date] ), [SSS Store Count Total])
SSS Flag =
SWITCH(
TRUE(),
[Store Age at Business Date]>547.501 &&
[CY Sales]>0 &&
[LY Sales]>0,
1,
""
)
Current Sales – Respect Open Close Dates =
CALCULATE([CY Sales],
FILTER(SSS,
[SSS Flag]=1
)
)
Last Year Sales Same Store – Respect Open Close Dates =
CALCULATE([LY Sales],
FILTER(SSS,
[SSS Flag]=1
)
)
LY Sales = Calculate([CY Sales], DATEADD('Day Key'[Business Date],-364,Day))
Hi @Quinnie2017,
You can try measure as:
IF(
HASONEFILTER(Table[Store Name]),
[Last Year Sales Same Store – Respect Open Close Dates],
[Last Year Sales Same Store – Respect Open Close Dates])
If it does not work, please provide sample data after removing sensitive information.
Best Regards,
Link
If this post helps then please consider Accept it as the solution to help the other members find it more quickly.
Would appreciate even the least bit of help pointing me in the right direction on this one as 24 hours later I'm still stuck. 😞 Thanks in advance!
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
111 | |
100 | |
80 | |
64 | |
58 |
User | Count |
---|---|
146 | |
110 | |
93 | |
84 | |
67 |