Count and graph multiple dates in the same table

[psmurphy]

Hi,

I have searched for a solution and cannot find anything that is obvious but this must be a common scenario

I have a table with 5 seperate dates that means the same thing just done 5 times and usually on different dates and I need to total\sum\count these so I can graph it to get a single total across these dates for each month\year

Below is the table\fields

Data

I have come across using a dates table and USERELATIONSHIP but the examples and solutions are a little beyond my capability and assume some indepth knowledge of PowerBI

How would I go about solving this, a simple step by step solution would be fantastic

[Ritaf1983 · Accepted solution]

Hi @psmurphy 
Unfortunately, there's no simpler way to work with different dates from an active primary relationship and other inactive relationships while leveraging the USERELATIONSHIP function as you mentioned. Let me try to explain with a simple example. When you visualize multiple measures based on a single date column, it implies that date is a common field between all of them, but the events occurring on that date can vary. For instance, in an orders table, there's an order date and a shipping date, and typically an order isn't shipped on the same day it's created. To enable counting orders with different statuses (created/shipped) based on a common date field, we need to ensure that the relationship with the calendar table for the specific measure only considers the relevant date. This is why Power BI allows us to create multiple inactive relationships, or as I call them "dormant" relationships, which remain inactive until we explicitly activate them within a measure using the USERELATIONSHIP function.

Back to the example of orders to make it works the model should look like this:

2 relationships with the calender :
1. Active - by order date
2. inactive - by ship date

Now lets say I want to show created and shipped orders by month.
I'll need to create 2 measures :

orders created  qty = DISTINCTCOUNT('orders'[Order ID])

**here I don't need any manipulations because this will work with the active ralationship

shipped orders = CALCULATE(DISTINCTCOUNT(orders[Customer ID]),USERELATIONSHIP(orders[Ship date],'Calendar'[Date]))

**the same logic , but with the activating a needed relationship

Result:

The pbix with the example is attached

If this post helps, then please consider Accepting it as the solution to help the other members find it more quickly

[mickey64 · Accepted solution]

For youre reference.

Step 0: I use these data below.

Step 1: I demote headers on Power Query Editor.

- Before -

- After -

Step 2: I merge columns for 5 times.

Step 3: I promote these headers.

Step 4: I unpivot these columns.

Step 5: I split 'Attribute' column by '_'.

Step 6: I split 'Value' column by '_'.

Step 7: I filter 'Value.2' Column.

Step 8: I make a matrix.

[Ritaf1983 · Accepted solution]

Hi @psmurphy 
Unfortunately, there's no simpler way to work with different dates from an active primary relationship and other inactive relationships while leveraging the USERELATIONSHIP function as you mentioned. Let me try to explain with a simple example. When you visualize multiple measures based on a single date column, it implies that date is a common field between all of them, but the events occurring on that date can vary. For instance, in an orders table, there's an order date and a shipping date, and typically an order isn't shipped on the same day it's created. To enable counting orders with different statuses (created/shipped) based on a common date field, we need to ensure that the relationship with the calendar table for the specific measure only considers the relevant date. This is why Power BI allows us to create multiple inactive relationships, or as I call them "dormant" relationships, which remain inactive until we explicitly activate them within a measure using the USERELATIONSHIP function.

Back to the example of orders to make it works the model should look like this:

2 relationships with the calender :
1. Active - by order date
2. inactive - by ship date

Now lets say I want to show created and shipped orders by month.
I'll need to create 2 measures :

orders created  qty = DISTINCTCOUNT('orders'[Order ID])

**here I don't need any manipulations because this will work with the active ralationship

shipped orders = CALCULATE(DISTINCTCOUNT(orders[Customer ID]),USERELATIONSHIP(orders[Ship date],'Calendar'[Date]))

**the same logic , but with the activating a needed relationship

Result:

The pbix with the example is attached

If this post helps, then please consider Accepting it as the solution to help the other members find it more quickly