Howdy! So fairly complicated question coming up.
Let’s say you have 10 or so new retail locations open up during the year, and you want to benchmark how well each one doing by measuring how each one is ramping relative to each other. However, obviously, they all open at different dates. Ideally, we could compare each one at common intervals (x days out, y weeks out, etc). A key question to answer is: at period X (say one month out) how is a given store doing relative to our benchmark, our expectations, and the rest of our stores? And how is it trending?
So the ideal output is an X/Y line graph, where the X-axis represents time since opening, and Y-axis represents some numerical metric (typically revenue, but also customer visits, or new customers) over time on a cumulative basis.
In Excel, this task is fairly simple. In SQL, it’s doable. But DAX is another beast. I’ve set up another date table, but the fact that there’s so many stores makes it hard and the SWITCH command doesn’t really make sense. Let’s give an example.
Store 1: Jan 10, 2018
Store 2: Jan 30, 2018
Store 3: March 15, 2018
Store 4: July 5, 2018
Store 5: August 8, 2018
Store 6: November 11, 2018
Store 7: December 20, 2018
To get Day 1 revenue, the DAX formula basically has to go get the revenue for January 11 for Store 1, Jan 31 for Store 2, March 16 for Store 3…and December 21 for Store 7.
I’ve tried Related / etc, but to no avail.
I can roughly get the date table up and created, since the logic isn’t very hard. But the revenue query is hard. Because it needs to SUM to the other date table AND somehow reference the location_table to figure out which one is active (but they all are, since they are all being graphed) and then pick the correct trend. I guess I could make 10 measures, however with more store openings in 2019, that does not seem prudent.
Currently, I just pull the dates back in SQL and make a custom view, which is very limiting.
Any ideas? Can mock up a diagram, or an excel or PowerBI, however this seems more like a conceptual understanding of DAX question.
Sorry for how hard this is.