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've read other posts that are slightly different than my needs.
SvcDone-Join = [ServiceDone]-[JoinDate]
The result is number of days it took to Deploy a PC.
This number should exclude weekends and U.S. holidays if the SPAN of the 2 dates includes weekends or holidays. Not just for this year, but for each future year, as well.
I've seen this:
Dates = ADDCOLUMNS ( CALENDAR ( DATE ( 2019, 1, 1 ), DATE ( 2019, 12, 31 ) ), "Day Name", FORMAT ( [Date], "ddd" ), "Weekday", IF ( WEEKDAY ( [Date], 2 ) >= 6, 0, 1 ) )
But that's only 2019 instead of all years. Is there a way to use that formula for any year? Or is there another calculation?
TIA
Solved! Go to Solution.
Hello @stackedcreative
"But that's only 2019 instead of all years. Is there a way to use that formula for any year?"
You can change the parameters in the date function to include any year(s).
Dates =
ADDCOLUMNS (
CALENDAR ( DATE ( 2019, 1, 1 ), DATE ( 2019, 12, 31 ) ),
"Day Name", FORMAT ( [Date], "ddd" ),
"Weekday", IF ( WEEKDAY ( [Date], 2 ) >= 6, 0, 1 )
)
TO
Dates =
ADDCOLUMNS (
CALENDAR ( DATE ( 2019, 1, 1 ), DATE ( 2022, 12, 31 ) ),
"Day Name", FORMAT ( [Date], "ddd" ),
"Weekday", IF ( WEEKDAY ( [Date], 2 ) >= 6, 0, 1 )
)
This will create calendar year upto 2022.
Regards
Kumail Raza
Did this help? Kudos are appreciated
Consider Accept it as the solution to help the other members find it more quickly
Hi @stackedcreative ,
Try the following steps:
Step1,use the following dax ,to create data table:
Dates = CALENDAR("2019/1/1","2021/12/31")
Step2, import holiday in 2016 and 2017,refer:
https://blog.enterprisedna.co/creating-a-calendar-table-for-holidays-in-power-bi/
And import web link :
https://www.calendarpedia.com/holidays/federal-holidays-2019.html
https://www.calendarpedia.com/holidays/federal-holidays-2020.html
https://www.calendarpedia.com/holidays/federal-holidays-2021.html
And you will see the below:
Step 3, base on dates,and holiday table ,to create a new table ,use the following dax(when weekend and holiday ,the column return false)
date1 =
GENERATE (
CALENDARAUTO (),
VAR yyyy =
YEAR ( [Date] )
VAR mmmm =
MONTH ( [Date] )
RETURN
ROW ("Date1",FORMAT ( [Date], "YYYY-MM-DD" ),
"Year", yyyy,
"Month", FORMAT ( [Date], "mmmm" ),
"Month2", mmmm,
"Week1", FORMAT ( [Date], "dddd" ),
"Week2", WEEKDAY ( [Date] ),
"weekday", NOT WEEKDAY ( [Date] ) IN { 1, 7 } && not [Date] in VALUES('2019'[Date])&& not [Date] in VALUES('2020'[Date])&& not [Date] in VALUES('2021'[Date])
)
)
You will see the below:
The below due to I without your pbix file,may be you need to do some adjust:
difforder =
VAR day1 =
CALCULATE (
COUNTROWS ( 'date1' ),
DATESBETWEEN ( date1[Date], TableName[JoinDate], TableName[ServiceDone] ),
date1[weekday] <> TRUE,
ALL ( TableName)
)
VAR day2 =
IF ( day1 = BLANK (), 0, day1 )
VAR day3 =
DATEDIFF ( TableName[JoinDate], TableName[ServiceDone], Day) - day2
RETURN
day3
Wish it is helpful for you!
Best Regards
Lucien
Hi @stackedcreative ,
Try the following steps:
Step1,use the following dax ,to create data table:
Dates = CALENDAR("2019/1/1","2021/12/31")
Step2, import holiday in 2016 and 2017,refer:
https://blog.enterprisedna.co/creating-a-calendar-table-for-holidays-in-power-bi/
And import web link :
https://www.calendarpedia.com/holidays/federal-holidays-2019.html
https://www.calendarpedia.com/holidays/federal-holidays-2020.html
https://www.calendarpedia.com/holidays/federal-holidays-2021.html
And you will see the below:
Step 3, base on dates,and holiday table ,to create a new table ,use the following dax(when weekend and holiday ,the column return false)
date1 =
GENERATE (
CALENDARAUTO (),
VAR yyyy =
YEAR ( [Date] )
VAR mmmm =
MONTH ( [Date] )
RETURN
ROW ("Date1",FORMAT ( [Date], "YYYY-MM-DD" ),
"Year", yyyy,
"Month", FORMAT ( [Date], "mmmm" ),
"Month2", mmmm,
"Week1", FORMAT ( [Date], "dddd" ),
"Week2", WEEKDAY ( [Date] ),
"weekday", NOT WEEKDAY ( [Date] ) IN { 1, 7 } && not [Date] in VALUES('2019'[Date])&& not [Date] in VALUES('2020'[Date])&& not [Date] in VALUES('2021'[Date])
)
)
You will see the below:
The below due to I without your pbix file,may be you need to do some adjust:
difforder =
VAR day1 =
CALCULATE (
COUNTROWS ( 'date1' ),
DATESBETWEEN ( date1[Date], TableName[JoinDate], TableName[ServiceDone] ),
date1[weekday] <> TRUE,
ALL ( TableName)
)
VAR day2 =
IF ( day1 = BLANK (), 0, day1 )
VAR day3 =
DATEDIFF ( TableName[JoinDate], TableName[ServiceDone], Day) - day2
RETURN
day3
Wish it is helpful for you!
Best Regards
Lucien
Hello @stackedcreative
"But that's only 2019 instead of all years. Is there a way to use that formula for any year?"
You can change the parameters in the date function to include any year(s).
Dates =
ADDCOLUMNS (
CALENDAR ( DATE ( 2019, 1, 1 ), DATE ( 2019, 12, 31 ) ),
"Day Name", FORMAT ( [Date], "ddd" ),
"Weekday", IF ( WEEKDAY ( [Date], 2 ) >= 6, 0, 1 )
)
TO
Dates =
ADDCOLUMNS (
CALENDAR ( DATE ( 2019, 1, 1 ), DATE ( 2022, 12, 31 ) ),
"Day Name", FORMAT ( [Date], "ddd" ),
"Weekday", IF ( WEEKDAY ( [Date], 2 ) >= 6, 0, 1 )
)
This will create calendar year upto 2022.
Regards
Kumail Raza
Did this help? Kudos are appreciated
Consider Accept it as the solution to help the other members find it more quickly
@stackedcreative . Work day as measure and column
measure
Work Day = COUNTROWS(FILTER(ADDCOLUMNS(CALENDAR(Min(Table[ServiceDone]),Max(Table[End Date])),"WorkDay", if(WEEKDAY([Date],2) <6,1,0)),[JoinDate] =1))
Column
Work Day = COUNTROWS(FILTER(ADDCOLUMNS(CALENDAR(Table[ServiceDone],Table[End Date]),"WorkDay", if(WEEKDAY([JoinDate],2) <6,1,0)),[WorkDay] =1))
else you need to create a calendar with holiday and use that for date diff
https://radacad.com/create-a-date-dimension-in-power-bi-in-4-steps-step-3-public-holidays
Refer how calculate -How to calculate Business Days/ Workdays, with or without date table: https://youtu.be/Qv4wT8_P-AA
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 |
---|---|
109 | |
98 | |
77 | |
66 | |
54 |
User | Count |
---|---|
144 | |
104 | |
100 | |
86 | |
64 |