Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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!
Solved! Go to Solution.
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
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! 🙂
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 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!!
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! 🙂
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
105 | |
96 | |
75 | |
63 | |
62 |
User | Count |
---|---|
135 | |
105 | |
104 | |
80 | |
65 |