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




v-huizhn-msft's avatar
v-huizhn-msft
Microsoft Employee
8 years ago

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'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