The ultimate Microsoft Fabric, Power BI, Azure AI, and SQL learning event! Join us in Stockholm, Sweden from September 24-27, 2024.
2-for-1 sale on June 20 only!
Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started
Need help to calculate dynamic expected working hours and cost for the year, based on date selection and details as below:
Tables
Relationships
From:Emp Data[Emp ID] To:Cost[Emp ID] (1 to 1)
Expected hours = Workday * 8 for each employee, start date should be 2021/1/1 if joined before 2021/1/1 else start date and end date based on slicer selection
Expected Cost = Expected hours * Hourly Rate for each employee
I have achieved workdays calculation with help of below measures and it is working fine based on slicer changes, but not able exclude Weekend (Sat and Sun). any help will be highly apprecieated.
pbix file >> https://drive.google.com/file/d/1Bv5JZc9M6h0fqsB2iFWxALx-rS0fbgDV/view?usp=sharing
.Start Date Check =
VAR st =
SUMX ( 'Emp Data', 'Emp Data'[Start Date] )
VAR yr_st =
DATE ( 2021, 1, 1 )
RETURN
IF ( st > yr_st, st, yr_st )
.End Date Check =
VAR ter_dt =
SUMX ( 'Emp Data', 'Emp Data'[Termination Date] )
VAR ed = [End Date]
RETURN
IF ( ISBLANK ( ter_dt ) = TRUE (), ed, IF ( ter_dt < ed, ter_dt, ed ) )
WorkDays =
SUMX (
'Emp Data',
DATEDIFF ( [.Start Date Check], [.End Date Check], DAY )
)
Hi @AllG ,
", but not able exclude Weekend (Sat and Sun). any help will be highly apprecieated."
Please refer to the formula.
Measure =
CALCULATE(
DATEDIFF( [.Start Date Check], [.End Date Check], DAY ),
FILTER(
'Emp Data',
NOT( WEEKDAY([.Start Date Check]) in {6,7} ) && NOT(WEEKDAY([.End Date Check]) in {6,7} )
)
)
Best regards,
Lionel Chen
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi Lionel,
Thanks for your reply.
I am looking for dynamic calculation of workdays, based on start date from "Emp Data" table and end date from slicer selection. And I am not getting correct results with above calculate measue, for example, January workdays showing as 30 and group by or summarize results are blank.
Monday to Friday are working days, holidays not require.
Please clarify what you mean by workday. Which days are weekend in your country, and do you want to account for holidays?
Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.
Check out the June 2024 Power BI update to learn about new features.
User | Count |
---|---|
102 | |
97 | |
80 | |
62 | |
57 |
User | Count |
---|---|
264 | |
120 | |
115 | |
83 | |
70 |