The ultimate Microsoft Fabric, Power BI, Azure AI, and SQL learning event! Join us in Stockholm, Sweden from September 24-27, 2024.
2-for-1 sale on June 20 only!
Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started
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
Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.
Check out the June 2024 Power BI update to learn about new features.
User | Count |
---|---|
99 | |
93 | |
81 | |
63 | |
56 |
User | Count |
---|---|
247 | |
122 | |
110 | |
77 | |
72 |