Pb to calculate Leadtime

[lore256]

Hi,

I'm a beginner with Power Bi Desktop and I still discover this tool. I hope somebody can help me :).

I explain my problem from the beginning:

I must calculate the Leadtime between two movements "Take in charge" - "Drop off" with the same parcel, for example:

But I have some difficulties:

- I must keep only the parcels with these two movements

- I can't calculate the Leadtime with "datediff" because the dates are not on the same line.

I would like this result on Power BI:

How should I proceed to do that?

Thank you for your help.

Lore

[v-piga-msft · Accepted solution]

Hi @lore256,

Fo you scenario, you could create a calculated table with SUMMARIZE.

Please have a reference of the formula below and modify it for your requirement.

Table 2 =
SUMMARIZE (
    'Table',
    'Table'[Parcel Number],
    "Take in charge_", CALCULATE (
        MIN ( 'Table'[Movement data and time] ),
        FILTER ( 'Table', 'Table'[Movement label] = "Take in charge" )
    ),
    "Drop Off_", CALCULATE (
        MAX ( 'Table'[Movement data and time] ),
        FILTER ( 'Table', 'Table'[Movement label] = "Drop Off" )
    )
)

Column = 'Table 2'[Take in charge]-'Table 2'[Drop Off]

Here is the output.

Best Regards,

Cherry

[lore256]

Thank you very much @v-piga-msft for your answer. It works well!

I have just a last request please: How could I integrate only the working days in my leadtime (without week end and bank holiday)?

For information, I found this calendar table:

Thank you again

Best Regards, 

Lore

[v-piga-msft]

Hi @lore256,

It seems that you want ot calculate the working days.

You could have a reference of the links below.

1. Using Power Query: https://www.powerquery.training/portfolio/networkdays/

    Video

2. Using DAX: https://community.powerbi.com/t5/Desktop/DATEDIFF-Working-Days/td-p/130662

Best Regards,

Cherry

[lore256]

Hi @v-piga-msft (Cherry),

Thank you but I already read these solutions but that not corresponds to my request...  . I don't want to calculate the total of working days. I would like the leadtime with only the working days (in particulary hour) for each lines. 

Thank you

Best Regards,

Lore