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
pravinW007's avatar
pravinW007
Regular Visitor
1 year ago

Hi amit_wairkar ,

Another option you can try first check weekday of your project  Var end_date = weekday(End Date)
it will return 1 for Sunday and 7 for sat.
then check how many days you want to add.
var no_of_days=
if end date is sunday add 2 extra days to no of days = 2 + 10 =12
if end date is Sat add 3 extra days to no of days = 3 + 10 =13 else 14 days.
final result would be PRojec_End date = end_date  + no_of_days.

 

If this solve your problem, please mark "Accept as Solution"


Thanks,
Pravin Wattamwar
Linkedin : https://www.linkedin.com/in/pravin-p-wattamwar/