Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

Business days between two dates

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":

 

pbi1.jpg


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

Thanks in advance!

 

 

2 ACCEPTED SOLUTIONS
amitchandak
Super User
Super User

@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

View solution in original post

Anonymous
Not applicable

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! 🙂

View solution in original post

4 REPLIES 4
amitchandak
Super User
Super User

@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
Not applicable

@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!

 

 

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

 

Anonymous
Not applicable

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! 🙂

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.