Forum Discussion

amit_wairkar's avatar
amit_wairkar
Frequent Visitor
1 year ago
Solved

Derive an End date

Hi All,   I want to calculate the End date of a project based on the no. of working days. So the formula currently i have is Today + Total no. of days.   Eg: End Date = 20/05/2025 + 10 days = 30...
"DAX "
  • amit_wairkar's avatar
    amit_wairkar
    1 year ago

    Hearty Thanks for all your support. I tried everyones suggestion but faced some issue. I was able to crack the formula with some help. Below is the DAX:

    VAR RemainingDays = A Formula
    VAR TodayDate = TODAY()-1
     
    RETURN
    CALCULATE (
        MAX('Dates'[Date]),
        FILTER (
            ADDCOLUMNS (
                FILTER (
                    'Dates',
                    'Dates'[Date] >= TodayDate &&
                    'Dates'[IsWorkingDay] = TRUE
                ),
                "WorkDayIndex", RANKX (
                    FILTER (
                        'Dates',
                        'Dates'[Date] >= TodayDate &&
                        'Dates'[IsWorkingDay] = TRUE
                    ),
                    'Dates'[Date],
                    ,
                    ASC
                )
            ),
            [WorkDayIndex] = RemainingDays
        )
    )
     
    I have a Date table which has a column [IsWorkingDay] =
    IF (
        WEEKDAY('Dates'[Date], 2) <= 5, // Monday = 1, ..., Friday = 5
        TRUE,
        FALSE
    )
    Regards,
    Amit Wairkar
maruthisp's avatar
maruthisp
Icon for Super User rankSuper User
1 year ago

Hi amit_wairkar,

Please find below DAX expression as per the requirements.
Project End Date (no calendar) =
VAR _Start = [ProjectStart]
VAR _Days = [WorkDays]
VAR _Buffer = _Days * 3
VAR _Cand =
ADDCOLUMNS(
CALENDAR( _Start+1, _Start + _Buffer ),
"IsWork", WEEKDAY( [Date], 2 ) < 6 // 1=Mon … 5=Fri, 6=Sat, 7=Sun
)
VAR _WorkList =
FILTER( _Cand, [IsWork] = TRUE )
VAR _FirstN =
TOPN( _Days, _WorkList, [Date], ASC )
RETURN
MAXX( _FirstN, [Date] )

Please let me know if you have further questions.

If this reply helped solve your problem, please consider clicking "Accept as Solution" so others can benefit too. And if you found it useful, a quick "Kudos" is always appreciated, thanks! 

 

Best Regards, 

Maruthi 

LinkedIn - http://www.linkedin.com/in/maruthi-siva-prasad/ 

X            -  Maruthi Siva Prasad - (@MaruthiSP) / X