Business days between two dates

[Anonymous]

Hey guys,

First of all - I know there's a lot of topics on related cases, but I couldn't find the solution to mine, tried most of them.

I have a function that it's counting the days between one date and today: 

Days in current status = datediff (if (Tasks[Task Status] = "Open", Tasks[Created].[Date], TODAY()), TODAY(), DAY)

 The trick I'm facing here is that "Tasks" table is storing multiple rows and I need to filter for the latest one (that it's marked as "Open").
I have created a new query called DateCal which is counting weekdays as "1" and weekends as "0":

I can't find a way to display them as weekdays, can somebody advise?

Thanks in advance!

[amitchandak · Accepted solution]

@bartekjgs

What I've done is use a calendar table with working days. Instead of the second date, you can use today(). There is no need for an aggeration function on other dates.

refer :https://www.dropbox.com/s/y47ah38sr157l7t/Order_delivery_date_diff.pbix?dl=0

[amitchandak · Accepted solution]

@bartekjgs

What I've done is use a calendar table with working days. Instead of the second date, you can use today(). There is no need for an aggeration function on other dates.

refer :https://www.dropbox.com/s/y47ah38sr157l7t/Order_delivery_date_diff.pbix?dl=0

[Anonymous]

@amitchandak not quite sure if I understand what you mean... I have came up with a little different, simpler solution, but the file you have shared has given me some useful tips, so thanks for that!

[amitchandak]

@Anonymous , If possible please also post your solution. So that it can help others. Kudos to you!!

[Anonymous · Accepted solution]

Of course @amitchandak,
so basically what I've done is revise the function a bit:

Business days in current status = 
var Opentaskdate = if(Tasks[Task Status] = "Open", Tasks[Created].[ Date], TODAY())
return

CALCULATE(
    COUNTROWS ( DateCal),
    DATESBETWEEN(DateCal[Data].[ Date],  Opentaskdate, TODAY()),
    DateCal[IsWorkday] = 1,
    ALL ( DateCal[Data] )
)

Now it serves my purpose as desired! 🙂