Forum Discussion
DATEDIFF across multiple tables
Hello,
I am stuggling with the following situation, any advice would be highly appreciated.
I have 4 Dimension columns in my AS model:
- Requisitions [Requisition Date]
- Purchase Orders [PO Date]
- Invoices [Invoice Date]
- Payments [Payment Date]
There is 1 fact table connected to all 4 dimension tables.
I want a column/measure in the fact table that shows the difference in days between:
1. Requisition Date and Payment Date
Else, if the requisition date is empty:
2. Purchase Order Date and Payment Date
Else, if the PO Date is empty:
3. Invoice Date and Payment Date
I created measures on the 4 date columns in their own dimension tables and tried the below measure in the fact but that doesn't work:
IF(NOT(ISBLANK('Dim - Purchase Requisitions'[Requisition Creation Date Measure])), DATEDIFF('Dim - Purchase Requisitions'[Requisition Creation Date Measure],'Dim - Purchase Payments'[Payment Date Measure],DAY),
IF(NOT(ISBLANK('Dim - Purchase Orders'[PO Header Creation Date Measure])),DATEDIFF('Dim - Purchase Orders'[PO Header Creation Date Measure],'Dim - Purchase Payments'[Payment Date Measure],DAY),
DATEDIFF('Dim - Purchase Invoices'[AP Invoice Date Measure],'Dim - Purchase Payments'[Payment Date Measure],DAY))
)
Do I need a different way to approach this? I am using Visual Studio by the way to manage the AS model.
4 Replies
- amitchandak
Super User
Anonymous , If you have one fact that has 4 date, all 4 of them should join to one date table. One active, three inactive, That you can join using userelation - https://radacad.com/userelationship-or-role-playing-dimension-dealing-with-inactive-relationships-in-power-bi
https://www.youtube.com/watch?v=e6Y-l_JtCq4
If you want all four to be analyzed on the same month like no of Requisition, no of PO in Jan 2020, then the above will work. But if you need them on different months Like Jan 2020 PO and Feb 2020 Requisition, you need separate date tables. They need to be date table.
Not sure how subject is related to description, for that refer my blog - https://community.powerbi.com/t5/Community-Blog/Decoding-Direct-Query-in-Power-BI-Part-2-Date-Difference-Across/ba-p/934397#M451
To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s. Refer :radacad sqlbi My Video Series Appreciate your Kudos.
amitchandak thanks for your reply, not sure I understand though.
The dates are present in 4 different dimension tables. Those dimension tables are connected to 1 fact table through ID's (PO ID, Requisition ID, Invoice ID and Payment ID).I need a DAX calculation that can check if the date in the dimension table is present. If yes, do a datediff on that date. If no, check the next dimension table date and so on.
- amitchandak
Anonymous ,Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.
can't easily extract the data, but it's basically like below:
Requisitions Dim table:
Requisition ID Requisition Creation Date 1 01-01-2020 2 02-01-2020 3 03-01-2020 4 04-01-2020 5 05-01-2020 Purchase Orders Dim table:
Purchase Order ID Purchase Order Creation Date 1 01-01-2020 2 02-01-2020 3 03-01-2020 4 04-01-2020 5 05-01-2020 Invoices Dim table:
Invoice ID Invoice Creation Date 1 01-02-2020 2 02-02-2020 3 03-02-2020 4 04-02-2020 5 05-02-2020 Payments Dim table:
Payment ID Payment Creation Date 1 01-03-2020 2 02-03-2020 3 03-03-2020 4 04-03-2020 5 05-03-2020 Fact table:
Requisition ID Purchase Order ID Invoice ID Payment ID Payment Amount 1 1 1 1 100 2 2 2 2 200 3 3 3 3 300 4 4 4 4 400 5 5 5 5 500 The fact table has a many to one relationship to each Dim table. The fact table does not contain the dates.
So the goal is to get the difference in days between the Payment Date and the earliest available other date in this order: Requisition Date (if not available then) Purchase Order Date (if not available then) Invoice Date.
In the above case, all Requisitions are available so I would expect something like this:
Requisition ID Purchase Order ID Invoice ID Payment ID Payment Amount Difference in Days 1 1 1 1 100 59 (1-JAN - 1-MAR) 2 2 2 2 200 59 (2-JAN - 2-MAR) 3 3 3 3 300 59 (3-JAN - 3-MAR) 4 4 4 4 400 59 (4-JAN - 4-MAR) 5 5 5 5 500 59 (5-JAN - 5-MAR) Hope this makes sense.
kind regards,
Thomas
