Calculating lead time

[linsce]

Dear colleagues,

I am struggling with creating a measure that will calculate the lead time in network days. For the sake of clarity I'll simplify my data model. I have data table that consists of three columns: ID, Date Created and Date Closed. Some of IDs do not have Date Closed. My Time lookup table contains Date, Week number and Year.

Example scenario:

Date Created - beginning of the week 1

Date Closed - middle of the week 5

ID – on the filter

Weeks – on rows

Measure – on values

Week     LT (network days)

1             5

2             10

3             15

4             20

5             23

6             (blank)

At the beginning I thought it will be a piece of cake but now, after hours spent on experimenting with DAX I have to admit that I don't know how to do it.

Any help appreciated.

Cheers

[v-huizhn-msft]

Hi @linsce,

I am still confused about your requirement. 

1. What's the network days? It is a field in your resource table? 

2. You want to calculate the lead time in network days, please share the rules of calculation.

3. If there is a relationship between the Fact table(including ID, Date Created and Date Closed) and Date(contains Date, Week number and Year) table.

Please share more details, so that we can post solution which is close to your needs.

Best Regards,
Angelia

[linsce]

Hello @v-huizhn-msft,

Thanks for the feedback and sorry for not being clear. Please find my answers below:

1. I meant working days (Mon-Fri) by that. Actually I managed to bulit a calculated column that counts that difference in my fact table.
2. Depending on the context coming from the rows (weeks) the lead time should be calculated in two ways:

• DATEDIFF(FactTable[Date Created], Max(DateTable[Date], 'Working Days') - if Date Closed is later than the end of current week
• DATEDIFF(FactTable[Date Created], FactTable[Date Closed], 'Working Days') - if Date Closed is in the current week

      3. Yes there is relationship - FactTable[Date Closed] <- DateTable[Date].

Hope that makes sense.

Kind regards,

Bartosz

[v-huizhn-msft · Accepted solution]

Hi @linsce,

First, please search the holidays, related holiday to date table, you will get a calculated column to recognize if a day is working, please review this thread.

Second, create a measure to get max date using the formula: MaxDate= Max(DateTable[Date]), and another measure to get current week:currectweek=MAX(DateTable[week]).

Finally, in factable table, please create a calculated column to get expected result.

working days =
IF (
    WEEKNUM ( FactTable[closed date] ) > [currentweek],
    CALCULATE (
        COUNT ( DateTable[Date] ),
        DATESBETWEEN ( DateTable[Date], FactTable[Date Created], [MaxDate] )
    ),
    IF (
        WEEKNUM ( FactTable[closed date] ) = [currentweek],
        CALCULATE (
            COUNT ( DateTable[Date] ),
            DATESBETWEEN (
                DateTable[Date],
                FactTable[Date Created],
                FactTable[Date Closed]
            )
        ),
        0
    )
)

Best Regards,
Aneglia