topic Re: Calculating number of working days between TODAY and End Date (where TODAY is before/after End Date) in DAX Commands and Tips

Calculating number of working days between TODAY and End Date (where TODAY is before/after End Date)

annalisekerr — Fri, 11 Mar 2022 17:26:53 GMT

Hi, I am trying to add a new colum to a table which calculates the number of days between a particular (end) date and today. My problem is that some of the dates in the end date, is before todays date so I get an #ERROR message.

Ideally i'd like

If the End date is > than todays date, return the number of days

If the End Date is < than todays date, return a -number of days

I have tried to use the following

Please can anyone help?? I'm still very new to Power BI and DAX so still finding my feet 🤗

Re: Calculating number of working days between TODAY and End Date (where TODAY is before/after End Date)

Whitewater100 — Fri, 11 Mar 2022 18:51:04 GMT

Hi:

Can you try:

Calc Col =

var venddate = TableName[End Date]

var vthisday = TODAY()

return

DATEDIFF(venddate, vthisday,DAY)

it will give pos & neg numbers.

Re: Calculating number of working days between TODAY and End Date (where TODAY is before/after End Date)

tamerj1 — Tue, 15 Mar 2022 09:00:33 GMT

Hi annalisekerr
One way to that:

Workdays between today and end date = VAR CheckCondition = 'Iterations (2)'[Correct End Date] > 'Iterations (2)'[Todays Date] VAR MinDate = IF ( CheckCondition , 'Iterations (2)'[Todays Date], 'Iterations (2)'[Correct End Date] ) VAR MaxDate = IF ( CheckCondition , 'Iterations (2)'[Correct End Date], 'Iterations (2)'[Todays Date] ) VAR Result = COUNTROWS ( FILTER ( ADDCOLUMNS ( CALENDAR ( MinDate , MaxDate ), "Day Of Week", WEEKDAY ( [Date], 2 ) ), NOT ( [Day Of Week] IN { 6, 7 } ) ) ) RETURN IF ( CheckCondition , Result, - Result )

Re: Calculating number of working days between TODAY and End Date (where TODAY is before/after End Date)

annalisekerr — Mon, 14 Mar 2022 10:42:02 GMT

Thank you , I will give this a try

Re: Calculating number of working days between TODAY and End Date (where TODAY is before/after End Date)

annalisekerr — Mon, 14 Mar 2022 10:42:10 GMT

Thank you , I will give this a try

Re: Calculating number of working days between TODAY and End Date (where TODAY is before/after End Date)

Anonymous — Tue, 15 Mar 2022 08:40:48 GMT

Hi annalisekerr ,

I have a test tamerj1 's code and I find there is something wrong in it. It will show error : The start date in Calendar function can not be later than the end date.

Update Code:

Workdays between today and end date = VAR _CheckCondition = 'Iterations (2)'[Correct End Date] > 'Iterations (2)'[Todays Date] VAR _MinDate = IF ( _CheckCondition , 'Iterations (2)'[Todays Date], 'Iterations (2)'[Correct End Date] ) VAR _MaxDate = IF ( _CheckCondition , 'Iterations (2)'[Correct End Date], 'Iterations (2)'[Todays Date] ) VAR _Result = COUNTROWS ( FILTER ( ADDCOLUMNS ( CALENDAR ( _MinDate, _MaxDate ), "Day Of Week", WEEKDAY ( [Date], 2 ) ), NOT ( [Day Of Week] IN { 6, 7 } ) ) ) RETURN IF (_CheckCondition,_Result,-_Result)

Result is as below.

Best Regards,
Rico Zhou

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Re: Calculating number of working days between TODAY and End Date (where TODAY is before/after End Date)

tamerj1 — Tue, 15 Mar 2022 08:58:45 GMT

Thank you for the correction. I've created the variables but missed to use them 🙂 I will update my reply accordingly

Re: Calculating number of working days between TODAY and End Date (where TODAY is before/after End Date)

annalisekerr — Wed, 16 Mar 2022 08:55:09 GMT

Perfect, yes that works brilliantly... thank you

Re: Calculating number of working days between TODAY and End Date (where TODAY is before/after End Date)

tamerj1 — Wed, 16 Mar 2022 09:27:59 GMT

annalisekerr

Well, that was my solution.

Thanks anyway.
Good luck and have a great day!