Working Day calc not showing as negative

[AdamClarkRD]

I have a calculation to work out working days between 2 dates as per SQLBI, this works fine for positive dates. 

Mech Test CountDown =

CALCULATE(

    COUNTROWS ( 'Date'),

    DATESBETWEEN ('Date'[Date], Tracker[Expected Mech Test Result sent Date],TODAY()-1),

    'Date'[Working Day] = "Working day"

)

However, where the calculation is a negative, so the date column is earlier than today, it shows a positive count or 0, not the negative figure. 

When I swap the date and today within the DAYSBETWEEN, the result comes through as Zero (or blank)

I am assuming DAYSBETWEEN doesnt allow -ve numbers, but I have seen others return these. 

Any advice on returning the actual difference in days? To show -ve numbers?

[PhilipTreacy · Accepted solution]

Hi @AdamClarkRD 

Download PBIX file with below example

Try this

Mech Test Countdown = 

SWITCH(

    TRUE(),

    TODAY() > SELECTEDVALUE(Tracker[Expected Mech Test Result Sent Date]), CALCULATE(COUNTROWS('Date'), DATESBETWEEN('Date'[Date], SELECTEDVALUE(Tracker[Expected Mech Test Result Sent Date]), TODAY()), 'Date'[Working Day] = "Working Day") * -1,

    CALCULATE(COUNTROWS('Date'), DATESBETWEEN('Date'[Date], TODAY(), SELECTEDVALUE(Tracker[Expected Mech Test Result Sent Date])), 'Date'[Working Day] = "Working Day")

)

Regards

Phil

[Wilson_]

Philip,

One minor tweak to your measure: I think you need a negative on one of two calculations. (Not clear to me if they want to see the negative if the date is before or after today.)

[PhilipTreacy]

Thanks I just spotted that myself and fixed it!  Cheers

[Anonymous]

Hi @AdamClarkRD ,

I want to validate the valuable input provided by @PhilipTreacy . Their initial thoughts helped guide my approach. However, I noticed that more detail is needed to fully understand this.

The DATESBETWEEN function returns a table, so you need to use the COUNTROWS function to count the returned table, whereas the DATEDIFF function returns the number of intervals bounded between two dates, which is already a number, so you don't need to use the COUNTROWS function to count it.

Based on your description I have created some sample data and created calculated columns for them, hopefully it will inspire you.

If your Current Period does not refer to this, please clarify in a follow-up reply.

Best Regards,

Clara Gong

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

[PhilipTreacy]

Hi @AdamClarkRD 

DATESBETWEEN retunrs a table so you can't return a 'negative' number of rows in that table.

Try using DATEDIFF.  It will give positive and negative values for the number of days betwen dates 

Measure = DATEDIFF(today(), date(2024,1,21), DAY)

DATEDIFF function (DAX) - DAX | Microsoft Learn

Regards

Phil

[AdamClarkRD]

Hi, @PhilipTreacy I have tried this and it does not give me the correct results. 

Mech Test CountDown =

CALCULATE(

    COUNTROWS ('Date'),

    DATEDIFF(Tracker[Expected Mech Test Result sent Date],TODAY(), DAY),

    'Date'[Working Day] = "Working day"

)+0

[Wilson_]

AdamClarkRD,

If you look at the documentation @PhilipTreacy shared, DATEDIFF returns an integer. That's why you're seeing 1 for every column. It doesn't make sense to COUNTROWS an integer. However, since you want working days, if you want to use DATEDIFF, your measure will depend heavily on how your Date table is structured, since working days is not an out of the box calculation.