How to Work with Multiple Dates Within Your Data Tables in Power BI

[EnterpriseDNA]

Working with multiple dates within your data tables in Power BI is quite common, and many new Power BI users get confused with this. Not long ago, I posted an article here similar to this, but I think it’s a good idea to elaborate and give more examples. You can read the post, ‘How to Deal with Multiple Dates in Power BI’ here.

In the other article I mentioned “multiple dates within your data tables”; what I meant by this is that you have several dates in your fact table. In most of my tutorials and demonstrations, my fact table is Sales. But fact tables can be of any sort, depending on your industry and nature of work. So, it can be events, projects, etc. 

Once you start developing your Power BI data models, you’ll most likely have a situation where you have two or more dates in your fact table. The problem is you can’t place two relationships at a time between your fact table and the other table.  

You might think there’s a need for you to create another table, or perhaps there’s an issue within Power BI. However, this is not the case. There’s a thing called “inactive” relationship, and this is the key to learn.

The first video will show you how you can have multiple “inactive” relationships and one “active” relationship between your tables. Watch the video and learn how to achieve this by using the USERELATIONSHIP function within CALCULATE. 

You can see how you can “turn on” inactive relationships between your tables by using the correct DAX formula. 

In this tutorial, I work out the total number of staff I have at any timeframe. The technique that I use here is similar to working with multiple dates in a table wherein I need to run logic through that table to work out if an employee is still a staff member at any period. You can also apply this technique if you want to work out the growth in the number of staff through time. 

Check out how I use the correct relationship technique and the “events in progress” pattern to make this all work effectively in Power BI. 

Inactive relationships are needed to make this technique work. I hope you could grasp how I applied the multiple dates logic in this tutorial to work out the current staff population for multiple dates using the COUNTROWS and FILTER function. 

Key Takeaway 

Multiple dates scenarios can be very confusing, especially when you’re just staring out with Power BI. With these tutorials, you’ll have a better understanding of how to have multiple inactive relationships in your own models and activate them by using the correct DAX calculations. 

By using this technique, you’ll be able to filter your data by any of the dates. Furthermore, you’ll see that you can have a range of analyses and more insights that traditionally, with Excel, would have been very difficult and time-consuming to create. 

Once you understand and master this technique, you’ll be able to build more effective Power BI data models. 

Cheers! 

Sam 

***** Related Links ***** 

Manage Multiple Date Calculations In Your Fact Table – Advanced Power BI Technique 

How To Calculate Occupancy Days Per Month In Power BI Using DAX 

Calculating Staff Turnover In Power BI Using DAX - HR Insights 

***** Related Course Modules***** 

Time Intelligence Calculations 

Mastering DAX Calculations 

Unique Analytical Scenarios 

***** Related Support Forum Posts ***** 

How To Calculate Multiple Rows For A Condition 

Filter By Multiple Dates In The Same Table 

One Dimension, Multiple Facts And Multiple Interconnected Facts 

For more multiple date calculations support queries to review see here….