Time Intelligence between two Dates from different tables

[Anonymous]

Hi guys,

I have two tables, one table contains a column indicating when a delivery was shipped and the other table contains a column indicating when the requested delivery date was. I would now like to calculate the offset between these columns, but unfortunately this is not possible, because there are two tables. How can I calculate the offset in this example?

I hope you can help me! 😞

Abba1234!

[parry2k]

@Anonymous why not create a sample dummy data, you don't need to send the real data. How you expect someone to help you if you can provide the information. Good luck @MFelix 

[MFelix]

Hi @Anonymous ,

How do the two table relate? if they are related bu a delivery ID you can make the calculation.

Can you share some more information about your data? Please see this post regarding How to Get Your Question Answered Quickly (courtesy of @Greg_Deckler) and How to provide sample data in the Power BI Forum (courtesy of @ImkeF).

[Anonymous]

Hi @MFelix ,

yes there is a relation by the delivery ID, but how can i do the calculation? If I try it by measures, it cant find the second table with the column. I unfortunatly cant share the date because of privacy :/.

[MFelix]

Hi @Anonymous ,

No need for the actual data can you make a mocukp sample? What are the columns on your model, how do they relate?

If you want you can share the information trough private message.

Has you can imagine without any data is difficult to tell you what is the kind of measure you need to use, depending on the type of relationships, data, etc, the calculations vary.

[Anonymous]

Here i have a sample for both tables. The connection is by a created column "Key".

  

[MFelix · Accepted solution]

Hi @Anonymous ,

Based on the additional information you have provided regarding the use of a date table believe that the formulas can be the following:

Days = 
VAR Sumary_Table =
    FILTER (
        CROSSJOIN (
            SELECTCOLUMNS (
                Orders;
                "CLient_Key"; Orders[Key],
                "Desidered_date", RELATED('calendar'[Date])
            ),
            SELECTCOLUMNS (
                Delivery,
                "Sales_Key", Delivery[Key],
                "Delivery_date", Delivery[Delivery date]
            )
        ),
        [CLient_Key] = [Sales_Key]
    )
RETURN
    SUMX ( Sumary_Table, DATEDIFF ( [Desidered_date], [Delivery_date], DAY ) )

The second option you need to redo the measure to:

Days _ 2 = 
VAR Sumary_Table =
    SUMMARIZE (
        'Delivery (2)',
        'Delivery (2)'[Key],
        'Delivery (2)'[Delivery date],
        'Orders (2)'[Desidered Delivery Date]
    )
RETURN
    SUMX (
        Sumary_Table,
        DATEDIFF ( 'Orders (2)'[Desidered Delivery Date], 'Delivery (2)'[Delivery date], DAY )
    )

chehck PBIX file attach.

[Ashish_Mathur]

Hi,

In the first table, you can use the following calculated column formula

=lookupvalue('table2'[desired delivery date],'table2'[key],'table1'[key])

In another calculated column, you may subtract the 2 dates.

Hope this helps.

[MFelix]

Hi @Anonymous ,

Not sure if this matches your model but I have made the following setup:

Now create the following measure:

Days = 
VAR Sumary_Table =
    FILTER (
        CROSSJOIN (
            SELECTCOLUMNS (
                Client_Number,
                "CLient_Key", Client_Number[Key],
                "Desidered_date", Client_Number[Desidered Delivery Date]
            ),
            SELECTCOLUMNS (
                Sales_Document,
                "Sales_Key", Sales_Document[Key],
                "Delivery_date", Sales_Document[Delivery date]
            )
        ),
        [CLient_Key] = [Sales_Key]
    )
RETURN
    SUMX ( Sumary_Table, DATEDIFF ( [Desidered_date], [Delivery_date], DAY ) )

If your model however is only with the two table related you can do the following measure:

Days _ 2 =
VAR Sumary_Table =
    SUMMARIZE (
        'Client_Number (2)',
        'Client_Number (2)'[Key],
        'Client_Number (2)'[Desidered Delivery Date],
        "order dates", VALUES ( 'Sales_Document (2)'[Delivery date] )
    )
RETURN
    SUMX (
        Sumary_Table,
        DATEDIFF ( 'Client_Number (2)'[Desidered Delivery Date], [order dates], DAY )
    )

Has you can see result is the same in both cases:

Check PBIX file attach.