How To Deal With Multiple Dates In Power BI

EnterpriseDNA · ‎07-29-2019

What I wanted to do in this example is to show you how you can manage multiple dates in your Power BI tables.

This is a very common issue I see that new Power BI users are experiencing with their development work. There's always a bit of confusion on how you actually set up the data model correctly so that you can generate these insights that you need to work on across multiple dates and this mainly lies within the data model.

When I say multiple dates, I genuinely mean within your fact table. So, you might have a sales table which has an invoice date and a ship date or order date and dispatch date.

Even though I've used the sales example here, this is actually very common across lots of different business scenarios or function scenarios. Other examples could be around projects, events, staffing numbers, etc.

In this tutorial, I want to give you an idea how to solve these particular scenarios and work through some real-world case studies around how this can be applied within your models.

This first video tutorial covers this development technique in-depth and things you need to think about and how to apply these ideas within your models. From here, I'm going to show you how this can be applied to real case studies.

In this first example we're trying to calculate how many staff we have at any one time. We obviously know that a staff member starts at some particular point in time and then they eventually resign and leave the organization. We also know that they sometimes don't leave and so there will be no date at all for their leave date.

This tutorial dives into how you can actually solve all of these nuances in data that you might retrieve from say an HR system or staffing system.

For some further ideas around using Multiple Dates check out the link below ...

Calculating Staff Turnover In Power BI Using DAX - HR Insights

This next example is an even more unique one around occupancy days per month. In our raw data, we have multiple date scenario that we need to manage. In example a person is brought into a hospital versus when they left the hospital, we have both of those dates inside our fact table and we need to work out in any day or month how many people were in the hospital or how many beds were occupied in the hospital.

Again, we have to solve this in the data model and we also have to use a similar DAX formula technique as the example above to actually solve this.

This is really an interesting case study and visualization that we can create based on a matrix visual that we might want to see. Hopefully by working through this particular video tutorial you can gather a lot of information around how important the data model is but then also how reusable some DAX formula techniques are inside of Power BI.

There's a lot of great concepts wrapped up in these particular video tutorials and I think that if you can dive into them and really understand how it all works then you'll have no problem dealing with these multiple date scenarios when you come across them within your Power BI development.

All the best.

Sam

**** Learning Power BI? ****

FREE COURSE - Ultimate Beginners Guide To Power BI

FREE COURSE - Ultimate Beginners Guide To DAX

FREE - 60 Page DAX Reference Guide Download

FREE - Power BI Resources

Enterprise DNA Membership

Enterprise DNA Online

Enterprise DNA Events

****Related Links****

How To Work With Multiple Dates In Power BI