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.
Working minutes in POWER BI without holidays and no weekends. for the program from 09:00 to 17:00
I have two columns:
start_date and stop_date
NR | START | STOP |
129056770 | 20/12/2018 11:53 | 20/12/2018 11:53 |
129056771 | 21/12/2018 11:01 | 21/12/2018 11:12 |
129056772 | 27/12/2018 13:55 | 27/12/2018 13:55 |
129056773 | 11/12/2018 14:34 | 11/12/2018 14:34 |
129056774 | 14/12/2018 16:38 | 14/12/2018 16:42 |
Solved! Go to Solution.
Hi @Xcris22 ,
I create a sample based on your data model. You could reference it to modify your report.
Day = 'Table'[START].[Date]
WeekDay = WEEKDAY('Table'[START],2)
Holiday = RELATED(Holidays[Day])
If work day = IF(OR('Table'[WeekDay]=6,'Table'[WeekDay]=7),0,IF(ISBLANK('Table'[Holiday]),1,0))
Working minutes = CALCULATE(DATEDIFF(MAX('Table'[START]),MAX('Table'[STOP]),MINUTE),FILTER('Table','Table'[If work day]=1))
Edit :
If there is data that the start date and stop date are not same day in your table, you could reference the thread to have a try.
Best Regards,
Xue Ding
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly. Kudos are nice too.
@Xcris22 Read this post to get your answer quickly.
https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490
Please share data in excel using onedrive/google drive to get your answer quickly. Remove any sensitive information before sharing.
Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!
Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo
If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤
Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.
Done, thx for sugestion
Hi @Xcris22 ,
Based on your sample data, I created a calculated column using DATEDIFF function to implement it.
Working minutes = DATEDIFF('Table'[START],'Table'[STOP],MINUTE)
If you need a measure, please try the formula below :
Measure = DATEDIFF(MAX('Table'[START]),MAX('Table'[STOP]),MINUTE)
Best Regards,
Xue Ding
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
need to exclude our Romanian holidays. and the programs for work is 09:00 am-17:00 pm
Hi @Xcris22 ,
I create a sample based on your data model. You could reference it to modify your report.
Day = 'Table'[START].[Date]
WeekDay = WEEKDAY('Table'[START],2)
Holiday = RELATED(Holidays[Day])
If work day = IF(OR('Table'[WeekDay]=6,'Table'[WeekDay]=7),0,IF(ISBLANK('Table'[Holiday]),1,0))
Working minutes = CALCULATE(DATEDIFF(MAX('Table'[START]),MAX('Table'[STOP]),MINUTE),FILTER('Table','Table'[If work day]=1))
Edit :
If there is data that the start date and stop date are not same day in your table, you could reference the thread to have a try.
Best Regards,
Xue Ding
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly. Kudos are nice too.
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 |
---|---|
108 | |
100 | |
78 | |
64 | |
58 |
User | Count |
---|---|
148 | |
111 | |
94 | |
84 | |
67 |