Derive an End date

[amit_wairkar]

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/05/2025 as per calendar days

                                                        = 03/06/2025 as per working days

Thanks in Advance.

Regards,

Amit Wairkar

[v-echaithra]

Hi @amit_wairkar ,

Thank you for sharing your update and confirming that you dont have any issue. i request you to please accept your own post as the solution, this will help other community members who might face a similar issue.

Thanks again for your contribution!

[amit_wairkar · Accepted solution]

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

[v-echaithra]

Hi @amit_wairkar ,

As we haven’t heard back from you, we wanted to kindly follow up to check if the solution provided for the issue worked? or Let us know if you need any further assistance?
If our response addressed, please mark it as Accept as solution and click Yes if you found it helpful.

Regards,
Chaithra.

[pravinW007]

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/

[maruthisp]

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

[Jihwan_Kim]

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] )