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
amit_wairkar's avatar
amit_wairkar
Frequent Visitor
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