Creating a data model to track appointments over time

[jroob]

Hey all,

I am looking to reduce the manual reporting effort in Excel using PowerBI instead. I've got a spreadsheet that looks similar to the sample one below with some store information along with a column for the number of appointments opened. What I'm struggling with is the fact that the excel data is really just a summary of appointments without any underlying information about the individual appointments.

In PowerBI, I've got tables for Store information and a Date table, but I'd need to create the appointment table from scratch and then figure out how to make connections in a data model. If I do create an Appointment table, how could I connect them to the other two with a data model so that I could do things like 

- Create a summary of total appointments for January

- Create total appointments by store 

What I am thinking I may need to do is to create an entirely new Table for appointments to be able to look at individual ones instead of just a summary total.

Something like this

But, this is a lengthy process  to do manually as there are hundreds of appointments across many store locations. 

Any thoughts, advice, or feedback to point me in the right direction would be greatly appreciated.

Thanks,

[v-shex-msft]

Hi @jroob,

Did the above suggestions help with your scenario? if that is the case, you can consider Kudo or accept the helpful suggestions to help others who faced similar requirements.

If these also don't help, please share more detailed information to help us clarify your scenario to test.

How to Get Your Question Answered Quickly 

Regards,

Xiaoxin Sheng

[johnt75]

I'd definitely recommend a proper date table with year, month, quarter etc. I've found a week commencing column useful in the past, and you can use that to link the date table to your appointments table. the only downside is you will find it tricky when a week spans 2 months - which month should the appointments be assigned to.

[Whitewater100 · Accepted solution]

Hi:

Here is one possible way. Please see link. I guessed about appointment status, but this was more for model design.

I'll attach link for the pbix and excel in case you want to see what data I started with. Model diagram below. I hope this helps gets you going!

https://drive.google.com/file/d/15cPS2lK9ZeEcUhPeq21mDZNTYQYKf0RI/view?usp=sharing 

Excel

https://docs.google.com/spreadsheets/d/1RXHsn0y7mngiLWbow8y7jSK8pwJ4TeIF/edit?usp=sharing&ouid=10785... 

[jroob]

Wondering here if it'd be better to create a table with year, month, week, day, etc. and then somehow create filters or slicers to display the data. Or if I should put in the date table something like you said, end week column, and then connect the two through those columns. Guess it's the relationship between the two where I don't know much about. 

[johnt75]

You wouldn't necessarily need the individual appointments if you were only going to summarize anyway. You could create the Appointments with the Store ID, the week commencing date and the number of appointments. Link to the store table and to the week commencing column on your date table and you should be good to go.