Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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
Solved! Go to 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
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
Hello @v-huizhn-msft,
Thanks for the feedback and sorry for not being clear. Please find my answers below:
3. Yes there is relationship - FactTable[Date Closed] <- DateTable[Date].
Hope that makes sense.
Kind regards,
Bartosz
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