DATEDIFF across multiple tables

[Anonymous]

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.

[Anonymous]

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

[amitchandak]

@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.

[Anonymous]

@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.