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
Jihwan_Kim's avatar
Jihwan_Kim
Icon for Super User rankSuper User
1 year ago

Hi,

I am not sure how your semantic model looks like, but I tried to create a sample pbix file like below.

Please check the below picture and the attached pbix file.

 

 

INDEX function (DAX) - DAX | Microsoft Learn

 

 

expected result measure: = 
VAR _t =
    SUMMARIZE (
        FILTER (
            'calendar',
            'calendar'[Date] > TODAY ()
                && NOT ( 'calendar'[Week Day name] IN { "Sunday", "Saturday" } )
        ),
        'calendar'[Date]
    )
RETURN
    MAXX ( INDEX ( 10, _t, ORDERBY ( 'calendar'[Date], ASC ) ), 'calendar'[Date] )