Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
I'm using the following to calculate the age of an incident using working days only. It works perfectly for incidents that have a resolved date, but for incidents that don't have a resolved date it is counting non-working days as well.
I have a date table that shows Monday to Friday as 1s for working days, and Saturdays and Sundays as 0.
Can someone point out what wrong here? I'm an absolute novice and pieced this together from other solutions I'd found.
Thank you.
INC Age = IF(OR(ISBLANK('SN'[Created.Date1]),ISBLANK('SN'[Resolved.Date1])),
DATEDIFF('SN'[Created.Date1],TODAY(),DAY),
IF(OR
('SN'[Resolved.Date1] < 'SN'[Created.Date1],'SN'[Created.Date1] > 'SN'[Resolved.Date1]),
-1 *CALCULATE(COUNT('DateTable'[IsWorkDay]),FILTER('DateTable','DateTable'[Date] < SN[Created.Date1] && 'DateTable'[Date]>=SN[Resolved.Date1] && 'DateTable'[IsWorkDay] = 1)) + 0,
CALCULATE(COUNT('DateTable'[IsWorkDay]),FILTER('DateTable','DateTable'[Date] >= SN[Created.Date1] && 'DateTable'[Date]<'SN'[Resolved.Date1] && 'DateTable'[IsWorkDay] = 1)))) + 0
Solved! Go to Solution.
Hi @scabbyabbey,
Please try:
INC Age = IF ( OR ( ISBLANK ( 'SN'[Created.Date1] ), ISBLANK ( 'SN'[Resolved.Date1] ) ), CALCULATE ( COUNT ( 'DateTable'[IsWorkDay] ), FILTER ( 'DateTable', 'DateTable'[Date] >= SN[Created.Date1] && 'DateTable'[Date] < TODAY () && 'DateTable'[IsWorkDay] = 1 ) ), IF ( OR ( 'SN'[Resolved.Date1] < 'SN'[Created.Date1], 'SN'[Created.Date1] > 'SN'[Resolved.Date1] ), -1 * CALCULATE ( COUNT ( 'DateTable'[IsWorkDay] ), FILTER ( 'DateTable', 'DateTable'[Date] < SN[Created.Date1] && 'DateTable'[Date] >= SN[Resolved.Date1] && 'DateTable'[IsWorkDay] = 1 ) ) + 0, CALCULATE ( COUNT ( 'DateTable'[IsWorkDay] ), FILTER ( 'DateTable', 'DateTable'[Date] >= SN[Created.Date1] && 'DateTable'[Date] < 'SN'[Resolved.Date1] && 'DateTable'[IsWorkDay] = 1 ) ) ) ) + 0
Best regards,
Yuliana Gu
Hi @scabbyabbey,
Please try:
INC Age = IF ( OR ( ISBLANK ( 'SN'[Created.Date1] ), ISBLANK ( 'SN'[Resolved.Date1] ) ), CALCULATE ( COUNT ( 'DateTable'[IsWorkDay] ), FILTER ( 'DateTable', 'DateTable'[Date] >= SN[Created.Date1] && 'DateTable'[Date] < TODAY () && 'DateTable'[IsWorkDay] = 1 ) ), IF ( OR ( 'SN'[Resolved.Date1] < 'SN'[Created.Date1], 'SN'[Created.Date1] > 'SN'[Resolved.Date1] ), -1 * CALCULATE ( COUNT ( 'DateTable'[IsWorkDay] ), FILTER ( 'DateTable', 'DateTable'[Date] < SN[Created.Date1] && 'DateTable'[Date] >= SN[Resolved.Date1] && 'DateTable'[IsWorkDay] = 1 ) ) + 0, CALCULATE ( COUNT ( 'DateTable'[IsWorkDay] ), FILTER ( 'DateTable', 'DateTable'[Date] >= SN[Created.Date1] && 'DateTable'[Date] < 'SN'[Resolved.Date1] && 'DateTable'[IsWorkDay] = 1 ) ) ) ) + 0
Best regards,
Yuliana Gu
Thank you Yuliana, this worked!
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
111 | |
94 | |
83 | |
67 | |
59 |
User | Count |
---|---|
151 | |
121 | |
104 | |
87 | |
67 |