Forum Discussion

linsce's avatar
linsce
New Member
8 years ago
Solved

Calculating lead time

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...
  • v-huizhn-msft's avatar
    v-huizhn-msft
    8 years ago

    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




linsce's avatar
linsce
New Member
8 years ago

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's avatar
v-huizhn-msft
Microsoft Employee
8 years ago

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