Forum Discussion
Calculate days between dates in two separate tables
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
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])
5 Replies
- Datatouille
Solution Sage
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])
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 :)