Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
HI Guys new to this so trying to translate my excel skills to DAX !
I have two date columns and i want the working days between
iecolum A 1-june-2016 and column B 30-December 2016
current using the DATEDIFF ftn which is a good starting point
What im looking for is help with is :
a) how to deal with a a missing date - i want 0 for that row
b)if the date in colum A is greater thab B to rerun a negaive value
iwoould like to see something like that below giving results in column C
A B C
1/1/16 14/1/16 10 [ Working days]
1/1/16 BLANK 0
14/1/16 1/1/16 -10
Hope thats clear - thanks
Solved! Go to Solution.
In this scenario, you need to have a full calendar date table, then add a column to tag if the date is working day.
IsWorkingDay = IF(WEEKDAY('Calendar'[Date],2)>5,0,1)
Then you can create a calculated column like below:
NetWorkingDays = IF ( ISBLANK ( DateRange[StartDate] ) || ISBLANK ( DateRange[EndDate] ), 0, IF ( DateRange[StartDate] <= DateRange[EndDate], CALCULATE ( COUNT ( 'Calendar'[IsWorkingDay] ), DATESBETWEEN ( 'Calendar'[Date], DateRange[StartDate], DateRange[EndDate] ) ), - CALCULATE ( COUNT ( 'Calendar'[IsWorkingDay] ), DATESBETWEEN ( 'Calendar'[Date], DateRange[EndDate], DateRange[StartDate] ) ) ) )
Regards,
Networkdays = IF ( ISBLANK ( table[Date A] ) || ISBLANK ( table[Date B] ), 0, DATEDIFF ( table[Date A], table[Date B], day ) )
thanks - i had this but im not sure if your formula - [which is cleverer than mine !] will return network days or calander days ?
APP 2 Posting = if(OR
('Data'[First Fully Approved Date]=0,'Data'[First Sourced Date]=0)
,0,
(DATEDIFF
('Data'[First Fully Approved Date].[Date],'Data'[First Sourced Date].[Date],DAY)
))
In this scenario, you need to have a full calendar date table, then add a column to tag if the date is working day.
IsWorkingDay = IF(WEEKDAY('Calendar'[Date],2)>5,0,1)
Then you can create a calculated column like below:
NetWorkingDays = IF ( ISBLANK ( DateRange[StartDate] ) || ISBLANK ( DateRange[EndDate] ), 0, IF ( DateRange[StartDate] <= DateRange[EndDate], CALCULATE ( COUNT ( 'Calendar'[IsWorkingDay] ), DATESBETWEEN ( 'Calendar'[Date], DateRange[StartDate], DateRange[EndDate] ) ), - CALCULATE ( COUNT ( 'Calendar'[IsWorkingDay] ), DATESBETWEEN ( 'Calendar'[Date], DateRange[EndDate], DateRange[StartDate] ) ) ) )
Regards,
Hi
Thanks a lot for your formula, but there is a small issue I found that instead of using COUNT I used SUM now it is working fine