Calculate days between dates in two separate tables

[Anonymous]

 Hello, 

I have three tables:

Product (Fact) ([Product ID], [Estimated Delivery Date ID], [Actual Delivey Date ID])

Estimated Delivery Date (EDD Dim) ([Estimated Delivery Date ID], [Date])

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

Dim Tables for EDD and DD are two separate tables but are not directly related, except via their relationship with the Product fact table.

I would like to write three measures on the Product Table to:

1) Calculate the number of products where the EDD > DD

2) Calculate the average number of days/months/years etc between EDD and DD

3) For each product, calculate the number of days/months/years between EDD and DD

Could anyone suggest an approach to writing measures for any/all of the above?

Thanks!

Pbix

[Datatouille · Accepted solution]

HI @Anonymous

Are there any particular reasons for splitting into 3 distinct tables ?

In this particular case it might be worth denormalizing the data and replace the Date ID by the Dates in the Fact table - especially because the normalization here doesn't save that much space.. unless your product Table is VERY big (but still..).

You'd then have [Estimated Delivery Date] and [Delivery Date] in the same table (Product). And a simple calculated column computing: [Delivery Date] -  [Estimated Delivery Date] would help you a lot 🙂

If you have no control on your data model, you can use Related function in a calculated column (In Product Table) to import the dates from the Dimensions to the Product table using the IDs.

For example: create the following calc. column in the Product Table:

DD = Related(DDDim[Date])

[Anonymous]

Hi Excelside,

Thanks for the reply 🙂

Yes - limited control over the data model and tables are based on tables held in our warehouse staging DB that populates our SSAS instance - so i'm working with what I've got (without transforming datain Power BI...)

OK, so the best way to handle the below is to create two new calculated columns and use these new calc columns to calcuate date difference/KPIs? No problem if this is the case - I was just wondering if there was a way of wrapping all this up into one measure using a CALCULATE(COUNT(),...) expression?

Thanks!

Pbix

[Datatouille]

I rarely use calc. columns but in this case that's how I would do it but I might not have the best approach.

Let's see if other people tackle your question differently 🙂

[Anonymous]

Nice, thanks for taking the time to post! 🙂 It also made me think about whether I could use related() with countx() and maybe filter() to calculate late deliveries within a single measure? #daxnewbie

[Anonymous]

Well, this one has stumped me - I can't work out how to do a conditional count of records in one table based on comparing data in two other tables but I've tried @Datatouille approach and works well 🙂

Keen to learn though so if anyone knows whether it's possible to do a conditional count by evaluating dates in two other tables then I'd love to hear more! 🙂

Thanks

Pbix