Projected End date - only counting working days

[Anonymous]

Hello, 

I am stuck on a formula that I just can't seem to wrap my head around. I am attempting to build a visual that simply shows the estimated completion date based on current averages. 

I currently have a measure that tells me it's going to take "n" working days to complete the project at the rate we are averaging. 

What I'm attempting to do now is create a measure that returns this.
Today + The above measure (only counting weekdays) = what date? 

Here are my current date table and formal that isn't really getting me the results I want. 

Estimated Finish Date with weekends = CALCULATE( TODAY()+ [Estimated Working Days Till Completion] )

Date Year Month Number Month Year Month Number Week Number Numeric Day of Week Month Year IsWorkingDay Binary Weekday

Date	Year	Month Number	Month	Year Month Number	Week Number	Numeric Day of Week	Month Year	IsWorkingDay	Binary Weekday
1/1/2020	2020	1	January	24240	1	3	20-Jan	TRUE
1/2/2020	2020	1	January	24240	1	4	20-Jan	TRUE
1/3/2020	2020	1	January	24240	1	5	20-Jan	TRUE
1/4/2020	2020	1	January	24240	1	6	20-Jan	FALSE	1
1/5/2020	2020	1	January	24240	1	7	20-Jan	FALSE	1
1/6/2020	2020	1	January	24240	2	1	20-Jan	TRUE
1/7/2020	2020	1	January	24240	2	2	20-Jan	TRUE
1/8/2020	2020	1	January	24240	2	3	20-Jan	TRUE
1/9/2020	2020	1	January	24240	2	4	20-Jan	TRUE
1/10/2020	2020	1	January	24240	2	5	20-Jan	TRUE
1/11/2020	2020	1	January	24240	2	6	20-Jan	FALSE	1
1/12/2020	2020	1	January	24240	2	7	20-Jan	FALSE	1

[JarroVGIT]

Hi @Anonymous ,

Well this will work. Took me a while to figure it out, but I figured you can use RankX on a filtered datetable to get the desired output 🙂 I have used two tables, a datetable (DateTable) with column isWeekday (true or false) and the following test table (DaysToAdd):

The following measure gave me the desired result:

EndDateMeasure = 
IF(HASONEVALUE(DaysToAdd[Index]), 
    VAR curDate = SELECTEDVALUE(DaysToAdd[StartDate])
    VAR _daysToAdd = SELECTEDVALUE(DaysToAdd[DaysToAdd])
    VAR _filteredDateTable = FILTER(DateTable, DateTable[Date] >= curDate && DateTable[isWeekday] = TRUE)
    RETURN
    MAXX(FILTER(
    ADDCOLUMNS(_filteredDateTable, "@rank", RANKX(_filteredDateTable, [Date],, ASC)), [@rank] = _daysToAdd + 1), 
    [Date]))

Pulling the testtable and the measure in a Table visual will give you this:

Well this was a fun question to answer! Hope it helps, let me know if you have any questions!

Kind regards

Djerro123

-------------------------------

If this answered your question, please mark it as the Solution. This also helps others to find what they are looking for.

Keep those thumbs up coming! 🙂

[mwegener · Accepted solution]

Hi @Anonymous ,

try this

Estimated Finish Date with weekends =
VAR Today =
    TODAY ()
VAR WorkingDaysThisWeek =
    5 - WEEKDAY ( Today, 3 )
VAR WorkingDaysAfterThisWeek = [Estimated Working Days Till Completion] - WorkingDaysThisWeek
VAR DaysAfterThisWeek =
    INT ( DIVIDE ( WorkingDaysAfterThisWeek, 5, 0 ) ) * 7
        + MOD ( WorkingDaysAfterThisWeek, 5 )
RETURN
    IF (
        [Estimated Working Days Till Completion] < WorkingDaysThisWeek,
        Today + [Estimated Working Days Till Completion],
        Today + WorkingDaysThisWeek + 2 + DaysAfterThisWeek
    )

Hope this helps.

If I answered your question, please mark my post as solution, this will also help others.

Please give Kudos for support.