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

[annalisekerr]

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 🤗

[v-rzhou-msft · Accepted solution]

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.

[tamerj1]

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 )

[annalisekerr]

Thank you , I will give this a try

[Whitewater100]

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. 

[annalisekerr]

Thank you , I will give this a try

[v-rzhou-msft · Accepted solution]

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.

[annalisekerr]

Perfect, yes that works brilliantly... thank you 

[tamerj1]

@annalisekerr 

Well, that was my solution.

Thanks anyway.
Good luck and have a great day!

[tamerj1 · Accepted solution]

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