Hi,
How do I create a calculated column that counts the number of work days between two dates? Some of the start or end date can be blank till the project is complete. I have created a work day column in the dates table.
I tried the below without success.
DateDiff Days to Complete =
CALCULATE(
DATEDIFF('Project Management Data'[Start Date],'Project Management Data'[End Date],DAY),
'Dates'[Work Day] = TRUE(),
)
Thanks in advance.
Elizabeth
Check out this tutorial for ideas on how to do this.
Thanks
Assuming your Workday field is coded 1 for workday, 0 for weekend, I think this should work for you:
TotalWorkdaysBtw =
CALCULATE (
SUM ( Dates[Workday] ),
DATESBETWEEN (
Dates[Date],
'Project Management Data'[Start Date],
'Project Management Data'[End Date]
)
)Hi Brian,
Datesbetween for my situation which has no dates till job is complete, will not work because of the below. Cheer E
If StartDate is a blank date value, then StartDate will be the earliest value in the dates column.
If EndDate is a blank date value, then EndDate will be the latest value in the dates column.
The dates used as the StartDate and EndDate are inclusive: that is, if the sales occurred on September 1 and you use September 1 as the start date, sales on September 1 are counted.
If StartDate is larger than EndDate, the result is an empty table.
Can you post a sample PBIX?
Hi Brian,
I have a list of activities in a table that have a start date and end date. Some activities have not started hence no date, some activities have not ended hence have no date. I found the below formula counted the days between the two dates however non work days are also counted. I require the count days to only include work days and if no date in start date or end date to make 0 days.
Thank you in advance.
Elizabeth
An example file would be appreciated so some ideas can be tested.
Thanks
OK, I think this works per your conditions above (full PBIX posted below). Hope this is helpful.
Brian
Workdays Between =
VAR Startday =
SELECTEDVALUE ( ‘Project Management Data’[Start Date] )
VAR Endday =
SELECTEDVALUE ( ‘Project Management Data’[End Date] )
VAR Daterange =
DATESBETWEEN ( Dates[Date], Startday, Endday )
VAR Daterangeworkdays =
NATURALLEFTOUTERJOIN ( Daterange, Dates )
RETURN
IF (
Startday = BLANK (),
0,
IF ( Endday = BLANK (), 0, SUMX ( Daterangeworkdays, Dates[Workday Number] ) )
)
eDNA forum - workdays between.pbix (105.3 KB)
Hi Brian,
Where in the formula above does it exclude non work days. that is only include workdays.
Cheers
Elizabeth
The Workday Number column in the Dates table is coded 1 for workday, 0 for weekend. Thus, the SUMX of Workday Number in the final IF statement totals the workdays over the relevant date range for each task category (what I labeled “Project “ in my sample file).
Hi Brian,
I am hoping to count negative days to show a project was completed before forecast date. The dates between is not picking up negative days. Are you able to please adjust the formula to also pick up negative days.
Warm Regards
Elizabeth Tachjian
This is where Sam’s approaches of measure branching and use of variables really makes things simpler. If I’m understanding your last post correctly, I would suggest creating two new measures, in addition to the one I provided above:
Workdays Btw Start Date and Forecast End Date - all you have to do here is copy the DAX code over and change the Endday VAR in the [Workdays Between] measure to reference the forecast end date column in your Fact table, rather than the actual end date column.
Actual vs Target Workdays - this measure is just the difference between the [Workdays Between] and the [Workdays Btw Start Date and Forecast End Date] measures, and will show the negative total workdays for projects completed in advance of target date.
Good luck, and give a shout if you have any problems.
https://forum.enterprisedna.co/t/building-measures-on-measures-measure-branching/3215
https://forum.enterprisedna.co/t/how-to-use-variables-in-dax-formula-with-power-bi/468
Hi Brian,
I cannot see the measure Actual vs Target Workdays. There are two measures in the file as per below.
.
I did copy the workdays between measure but result does not show negative days.
I was hoping for a calculated column as I asked in the initial request. A calculated Column, so I can see the days diff result, and then create various measures on this column. Is a Calculated Column possible?
I have attached a copy of the pbix file I am practising from.
Cheers
ElizabethTest2.pbix (581.2 KB)
I see two significant problems getting to the results you want:
your test2 data seems insufficient. To implement all the measures needed for your analysis, I think you need three dates fields in your Fact table - a start date, a forecast completion date, and an actual completion date. You currently only have two of those.
calculating Workdays Between using calculated columns is going to be problematic for three reasons. Generally, you want to avoid creating calculated columns in your Fact table. In addition, calculated columns should be avoided when you are using aggregator functions and when you potentially will be changing filter context. I’m not sure if it would even be possible to do these as calculated columns, but regardless it’s not necessary since you can see the results of the measure(s) by putting them in a table or matrix visualization with the proper evaluation context, and you can easily build other measures off of them just as you could if they were calculated columns (i.e., measure branching).
If you can provide the revised Test2 database with all the necessary data fields per above, I think we can we can easily work through the remaining issues.
Here’s a terrific article that provides an in-depth explanation of the calculated columns vs measures issue:
Hi Brian,
Here is the updated pbix file to include a forecast date.
I am trying to work out the number of days the project started before forecast date. The number of days to exclude non work days and public holidays. To also calculate negative times to highlight when project started before forecast date.
Thank for helping me work through the calculation.
Cheers
ETest2.pbix (461.6 KB)
Cool. Thanks for hint re I can see the results using a table and measure branching instead of calculated columns.
OK, thanks for the updated sample file. I think it’s working just right now. Buckle in - long post ahead, but I’ll try to clearly explain what I did.
created Holiday Binary calculated column in your Public Holidays table. Set to 1 for every row in the table - this will serve as the target result for our LOOKUPVALUE function below.
Created three binary calculated columns in the Dates table:
a) Workday Binary - 1 for weekdays, 0 for weekends
Workday Binary =
SWITCH( Dates[DayInWeek],
5, 0,
6, 0,
1
)
b) Holiday Binary - 1 if date is found in Public Holidays lookup table, 0 if not found.
Holiday Binary DT =
VAR Holiday =
LOOKUPVALUE( 'Public Holidays'[Holiday Binary], 'Public Holidays'[Date], Dates[Date], 0)
RETURN
IF( Holiday = 1, 1, 0)
c) Workday Binary w Holidays - coded 1 if workday and not a holiday, 0 if otherwise. This is the column integral to the measures that follow - the other three are just interim steps to get to this point.
Workday Binary w Holidays =
IF ( Dates[Workday Binary] = 0, 0, IF ( Dates[Holiday Binary DT] = 1, 0, 1 ) )
We can check to make sure the logic is working correctly here by putting the binary columns into a table visual:
and sure enough, it behaves exactly as we expected it to. So far, so good.
Develop the measure to calculate workdays between the actual start date and end date. This is nearly identical to the measure we created week or so ago, the only significant difference being that we are now SUMXing the workday binary incorporating the holiday logic discussed above, whereas previously we were only totaling the workday binary. (Note: per Sam’s best practice, I have put this and all the measures below into a separate Key Measures table.)
Workdays Between Actual Start and End w Holidays =
VAR Startday =
SELECTEDVALUE ( 'Project Management Data'[Start Date] )
VAR Endday =
SELECTEDVALUE ( 'Project Management Data'[End Date] )
VAR Daterange =
DATESBETWEEN ( Dates[Date], Startday, Endday )
VAR Daterangeworkdays =
NATURALLEFTOUTERJOIN ( Daterange, Dates )
RETURN
IF (
Startday = BLANK (),
0,
IF (
Endday = BLANK (),
0,
SUMX ( Daterangeworkdays, Dates[Workday Binary w Holidays] )
)
)
Develop the measure to calculate workdays between the forecast start date and end date (Workdays Between Forecast Start and End w Holidays). Because we developed the above measure using variables, we can use the EXACT same DAX code as above, just changing the Startday variable to Forecast Start Date, instead of Actual Start Date.
Finally, create a simple measure quantifying in total work days (factoring in holidays) how much sooner (or later) the actual start date was relative to the forecast start date. In the way I’ve constructed this measure, positive numbers are “good”, reflecting an earlier than forecast start date, but if you want negative numbers to be “good”, obviously you can just flip the order of this measure expression around.
Total Workdays w Holidays Worked in Advance of Forecast Start =
[Workdays Between Actual Start and End w Holidays] - [Workdays Between Forecast Start and End w Holidays]
Here’s the table visual showing how all of this is working:
One note - you’ll notice that the first two rows of the visual have no value returned for the forecast start date measure. This is because in your data set the end date for these two tasks are earlier than the start date. Logically, this just seems to be a data entry error, but it returns an empty table in the DATESBETWEEN portion of the measure, and thus a blank value for the measure itself. Otherwise, I’ve checked the calculations against the calendar and your public holiday list and it seems to be working exactly as specified.
I hope this gets you what you need. Please give a shout if you have questions.
Full PBIX solution file posted below.
WOW. thank you Brian. I will review during the week.
