Counting events from different date dimensions occurring in the same period

[pbix]

Hello, 

I'm looking to write two measures to return simple counts of orders and deliveries within any period (e.g. every day, month, year) 

I have one fact table and two dimensions. These are:

1) Fact Sales ([Oorder ID],[Order Date ID], [Delivery Date ID])

2) Dim Order Date ([Order Date ID], [Order Date])

3) Dim Delivery Date ([Delivery Date ID], [Delivery Date])

When I select Order Date, I can get a simple count of orders in the period by using a measure like counta([Order ID]).

However, I also want to show the number of deliveries in the same period I selected for orders. 

How can I write a second measure that shows a count of deliveries in the same period? E.g. In January there were 700 orders and 500 deliveries.

Thanks 🙂

Pbix

[SqlJason]

How about something like this - 

Test =
VAR MaxOrderDate =
    MAX ( 'Dim Order Date'[Date] )
VAR MinOrderDate =
    MAX ( 'Dim Order Date'[Date] )
RETURN
    CALCULATE (
        COUNT ( 'Fact Sales'[orderID] ),
        ALL ( 'Dim Order Date' ),
        FILTER (
            ALL ( 'Dim Delivery Date' ),
            'Dim Delivery Date'[DateID] >= MinOrderDate
                && 'Dim Delivery Date'[DateID] <= MaxOrderDate
        )
    )

[pbix]

Hi @SqlJason

Thanks for your response! Judging by your handle i'd guess you're an SQL expert?

If I was writing this in sql it would be straightforward - 

select

dateperiod, 

count(case when orderdate between x and y then 1 end) OD,

count(case when deliverydate between x and y then 1 end) DD,

count(case when orderdate AND deliverdate between x and y then 1 end) ODED

from 

Table

group by

dateperiod

The below looks really good as a bespoke query! It feels though like I'm missing something in my set up - like I should just be able to write something simple like:

1) Test = 'Count of delivery dates' and

2) drag this to the report/visual canvas and it will calculate automatically whatever the date context is?

I suppose I'm interested in whether it's possible to develop a measure less bespoke than your great answer below - e.g. if I had 10 date dimensions, I could quickly compare any of these together, in whichever dimensions' date context, when they share a row context? Is this possible?

Thanks 🙂

pbix

[SqlJason · Accepted solution]

Maybe I am not clear what you want to see. But let me repeat what I understood and feel free to correct me if I am wrong. Right now, you have 2 date dimensions and one fact table, and you have also related those tables.

So let's say, you have a measure called

OrderCnt = count(Fact[OrderID])

Right now, if you choose something from the Order Date dimension or the Delivery date dimension, you will get the OrderCnt for the selection. For eg, if you choose Order Date = Jan, you will get OrderCnt where Order Date is Jan. (this is equal to "count(case when orderdate between x and y then 1 end) OD").

If you choose Delivery date = Jan, you will get OrderCnt where Delivery Date is Jan (this is equal to "count(case when deliverydate between x and y then 1 end) DD")

If you choose both Delivery date = Jan and Order Date = Jan, then you will get OrderCnt where Delivery Date and Order Date is Jan (this is equal to "count(case when orderdate AND deliverdate between x and y then 1 end) ODED")

Now, what I understood from your previous mail is that you just wanted to select the mnth only from the Order Date dimension, and the resultant measure should give the OrderCnt for Delivery Date for the same selection. So if you selected Jan for Order Date, then you should see OrderCnt for DeliveryDate = Jan. Looks like that is not what you want.

Now, in your SQL query, you have 3 measures - OD, DD and ODED based on whether you want to filter by Order Date, Delivery Date or both. Similarily, we will have to create 3 measures in Power BI - one for OD, one for DD and one for ODED. Now there are 2 approaches - 

1) You can create one dimension for each date (one for OD, one DD, etc). This will give you flexibility if you want a scenario where you wannt to find the count where Order Date = Jan and Delivery Date = Feb

2) You can create only one disconned date dimension and have inactive relationships from fact to the dimension. Then you can create individual measures where the relationships can be made active through DAX, and get one measure for OD, DD and ODED

If you have some sample data, and then also show me what you like as output, I can create a pbix file with that sample data and show you what to do.

[pbix]

Hi @SqlJason,

Thanks v much for your reply! After reading your v helpful explanation below I've had some time to think about this and I think the issue is my approach. I wanted to compare order dates and delivery dates in the same period using different date dimensions. So I changed my approach and am now handling this from via one master date calendar linked to [order date], [delivery date] in different columns via a single [dateid] and can compare different dates in the same period using CALCULATE((),USERELATIONSHIP()) and now appears to be working well!

Thanks! 🙂

pbix