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

In this tutorial, I’m going to teach you how to manage multiple date calculations in your Fact Table in Power BI. You may watch the full video of this tutorial at the bottom of this blog.

This is a frequent question in the Enterprise DNA Support Forum.

These multiple dates could be order dates, ship dates, expiration dates, and arrival dates, among many others. This information might be located within one row in your data set.

Here are some insights you can get out of this technique:

• The amount of orders currently being shipped
• The amount of orders waiting to be shipped
• The number of people working on a project
• The number of days the project has been going on

Writing the formulas for this technique can be confusing when you’re just starting out in Power BI.

With the detailed example in this tutorial, you’ll understand how to manage multiple date calculations in your Fact table.

## The Formula And Model

Let’s look at the model of the demo data set for this tutorial.

This kind of table can exist for order dates, expiration dates, and request dates, among others.

Setting up the model can be very confusing.

It’s a two-step process. One, the model needs to be correct. And two, the calculations should have the correct DAX formulas.

Now, the calculation needs to show the number of orders that are live for specific dates. In this case, live means orders that haven’t expired.

The formula needs to count information that sits between multiple dates. The context can vary depending on what you’re trying to solve, but the technique will be the same.

This is how the model should be set up:

You need to use inactive relationships.

For the calculations to be correct, you can’t have active relationships in the model.

When you want to calculate a logic that involves counting elements that are in progress, you need to have inactive relationships.

Having inactive relationships is the simplest method you can use to solve this problem. If you want to have active relationships, there are other advanced and complex methods that involve writing a new formula. However, this isn’t recommended.

Moving forward, you need to write a formula that uses the context in the Fact table and then works through an advanced logic inside the data table.

## A Better Understanding Through Visuals

To better understand how this technique works, look at this visualization:

The time frame in the visualization is showing how many orders there are between the order and expiry dates.

Between April and July 2019, you can see a huge spike indicating that many orders have expired:

This visualization makes it easier to understand what the technique is doing.

## Calculating The Active Orders

Here’s how to work out the number of orders that are still live and active.

The formula uses the COUNTROWS function to sort through the Data table.

Next, you need a formula that can count the orders you have for a certain day:

If the order date is less than and the expiration date greater than a certain day, it will count all the individual orders that belong within that date range.

For every single row in the table, there’s currently zero context coming from the model’s Fact table because there aren’t any active relationships.

Going back to the formula, the MAX and MIN functions are simply returning the date for every row in the table.

As an example, the March 21, 2019 row has 477 orders.

This is the result of iterating through every single order date within the 5,700 rows in the Date table. There are 477 orders that are less than the expiration date and greater than the order date.

The Order Between Dates formula will use that same logic for every row in the table.

Now, the Total you see in the table has no date context.

It is simply the sum of all the orders for each date that fits the criteria set by the FILTER statement in the formula.

## Why Using Inactive Relationships Is Important

Let’s go back to why inactive relationships are the key to making the model work.

Using inactive relationships in the model is cleaner. It also allows you to easily turn on any relationship depending on what you need for a certain calculation.

Here’s an example:

If you want to get the Total Orders by Order Date, you need an active relationship between the Date table and the Data table.

By using the USERELATIONSHIP function, you’re able to virtually turn on the relationship between those two tables within the formula.

This is how it looks in the table:

Under the third column, you’re only getting the number of orders per day for every row.

As an example, you can see that there are seven orders between the two consecutive rows:

Virtually turning on the relationships between tables allows you to branch out into different formula patterns.

## Conclusion

In this tutorial, you learned how to calculate the number of days between multiple dates in your Fact table using a specific formula pattern, and by creating inactive relationships in your model.

The FILTER statement along with its specific parameters is the key to arriving at the desired result.

Furthermore, being able to virtually turn on relationships between tables using the USERELATIONSHIP function in Power BI allows you to reuse this technique in different scenarios.

Cheers,

Sam

Sam McKay, CFA
Sam is Enterprise DNA's CEO & Founder. He helps individuals and organizations develop data driven cultures and create enterprise value by delivering business intelligence training and education.

## Formatting DAX Code In Power BI

Effort here will pay dividends later Placing effort into formatting your DAX code will make a huge...

## Cumulative Totals Based On Monthly Average Results In Power BI

Today, I wanted to cover a unique technique around cumulative totals based on monthly average results...

## Power Automate Expressions – An Introduction

In this tutorial, we’ll explore Power Automate expressions and how to use them in workflow automation....

## List.Max Power Query: User Guide With Examples

One of the most powerful and useful functions in Power Query M language is List.Max. It can be...

## R Scripting For Power BI Using RStudio

In this blog, we’re going to go through the basics of R scripting for Power BI using RStudio. The...

## Simple Aggregations In Power BI

In this blog post, I'll touch on some simple aggregations in Power BI that you can use in your DAX...

## Microsoft Report Builder: Steps In Creating A List

In this tutorial, you will learn how to create a list and page break in Microsoft Report Builder. A...

## 5 Strategies To Enhance Your Power BI DAX Skills

In this tutorial, you’ll learn the general strategies to improve your DAX or M capabilities. This is an...

## ALL Function in Power BI – How To Use It With DAX

Did you know that the ALL function can be used to modify the context of a particular calculation in...

## Calculations In Power BI Using Measure Branching

Measure Branching is a technique in making calculations in Power BI. It's not something you'll hear...

## Power BI Ranking In Hierarchical Form

Today, we will learn how to calculate Power BI ranking in a hierarchical form which is a little bit...

## Understanding Power BI Aggregations

Now, let's delve deeper into one of the most important concepts in Power BI calculations — the...