'Role-Playing' Multiple Date Dimensions to Index New Store Openings

[TexasBigData]

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.

Open Dates
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.

Thanks!

Best,
Bogdan

[Nick_M]

@TexasBigData
Could post of some sample data? I’m more of a visual person and numbers person, and that was a whole lotta text

[TexasBigData]

Book1.xlsx (23.8 KB)

Sure can! Sorry for the late reply. Atttached is a workbook with some randomly generated data. The end goal is to make the graph attached, on a variable basis.

[Nick_M]

@TexasBigData
Let’s see if this is going in the correct direction. Here’s the final output (dont worry, the file and what I did is below)

Steps:
I imported the revenue and the store open tables
Unpivotted the revenue table to get into tabular format
Merged the Store open table into the revenue table to get each store’s open date
With that could get a “days since open” column with this formula

![Days%20since%20open|690x359](upload://a2JPR5DcN1hrTABq5U1kLBgc5EQ.png)

Can remove the actual OpenDate column as no longer needed

Only need to load the Revenue table to the data model. Then just write the following measures to get the Revue and then the running total:

Total Rev = SUM ( FactRevenue[Revenue] )

Running Total = 
CALCULATE( 
    [Total Rev], 
    FILTER( 
        ALL ( FactRevenue[Days Since Open] ), 
        MAX ( FactRevenue[Days Since Open] ) >= FactRevenue[Days Since Open] )
)

Here’s the pbix file ( can click around in PQ to see exactly what happened there)
Role Playing.pbix (72.8 KB)

hope it helps, let me know if you have any questions

-Nick

[TexasBigData]

Thank you sir! Reviewing now with real data. This is unbelievable you could just produce this. Maybe it’s easy for you, but as someone relatively new to the Dax language, this is a huge breakthrough.

Thank you!

[Nick_M]

@TexasBigData
Glad it can help. And as long as you put in the time you will get there. What seems overly complicated is really just a series of small building blocks put together!

[TexasBigData]

Oh wow that worked. Thank you SO SO much! This is so incredibly immediately actionable and helpful. Thanks Nick!