Setting up correct data model

Power BI Data Model
1

If have an interesting scenario - My company puts on 2 Shows a year in the US - One in the Winter and one in Summer. Our Exhibit team is always working on booking exhibitors for multipleshows at the same time with overlapping dates…

For example:
Our winter show is in January 2020 - sales opened up on November 2018
Our winter show that was in January 2019 - sales open up in November 2017

Our summer show is in June 2019 - sales opened up on April 2018
Our summer show that was in June 2018 - sales open up in April 2017

Granted I know that there is only so much information I can give. I would like to put all of the “orders” in one table join it to a date table. Is there an easy way to do “to-date” calculations with multiple overlapping scenarios. See highlighted rows to show where the overlaps occur.

My goal is create some metrics for “pacing” to compare Winter TY/Winter LY and Summer TY/Summery LY - If that makes sense.

Any guidance /assistance will be greatly appreciated.

2019-07-01%2014_42_31-Book1%20-%20Excel
2019-07-01 14_42_31-Book1 - Excel.jpg448×712 61.8 KB

2

I don’t think this should be too difficult here.

First you want to unpivot this table. You should have only 3 columns here, one with the Month and the Other called something like Events and then the results in the third column.

From here you can then use TREATAS to created a virtual relationship between the date table (month & year column ) and this particular table.

This way you can then start using all the time intelligence functions from there.

See below for some info on TREATAS and how to use it.

See how you go with these ideas.

Thanks
Sam