topic Re: Derive an End date in DAX Commands and Tips

Derive an End date

amit_wairkar — Tue, 20 May 2025 03:14:13 GMT

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

Re: Derive an End date

Jihwan_Kim — Tue, 20 May 2025 03:33:03 GMT

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

Re: Derive an End date

maruthisp — Tue, 20 May 2025 05:34:58 GMT

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

Re: Derive an End date

pravinW007 — Tue, 20 May 2025 05:54:24 GMT

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/

Re: Derive an End date

v-echaithra — Tue, 27 May 2025 05:16:11 GMT

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.

Re: Derive an End date

amit_wairkar — Tue, 27 May 2025 06:51:23 GMT

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

Re: Derive an End date

v-echaithra — Wed, 28 May 2025 04:42:18 GMT

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!