Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Projected End date - only counting working days

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

DateYearMonth NumberMonthYear Month NumberWeek NumberNumeric Day of WeekMonth YearIsWorkingDayBinary Weekday
1/1/202020201January242401320-JanTRUE 
1/2/202020201January242401420-JanTRUE 
1/3/202020201January242401520-JanTRUE 
1/4/202020201January242401620-JanFALSE1
1/5/202020201January242401720-JanFALSE1
1/6/202020201January242402120-JanTRUE 
1/7/202020201January242402220-JanTRUE 
1/8/202020201January242402320-JanTRUE 
1/9/202020201January242402420-JanTRUE 
1/10/202020201January242402520-JanTRUE 
1/11/202020201January242402620-JanFALSE1
1/12/202020201January242402720-JanFALSE1
Need Help
  • mwegener's avatar
    mwegener
    6 years ago

    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.

2 Replies

  • mwegener's avatar
    mwegener
    Icon for Most Valuable Professional rankMost Valuable Professional
    6 years ago

    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.

  • JarroVGIT's avatar
    JarroVGIT
    Icon for Resident Rockstar rankResident Rockstar
    6 years ago

    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! 🙂