Saturday 27 February 2016

Finding No Of Work Hours

Challenge:-
Find the number of work hours between the Order Date and Ship Date. Assume a consistent 8 hour work day



Solution:-


1.Create a Calculated Field with name “Work Hours”  and enter in the following:.


This calculation may appear confusing, so let’s break this down .
 The first line in the calculation returns to us the total number of hours between our Order Date and Ship Date. Since we are assuming an 8 hour work day, we are NOT working 16 hours a day.

From our total difference in hours, we will now subtract the number of days times 16. This represents the total number of hours per day we are not working, so we will subtract this from the total number of hours between our dates.

Lastly, we still must account for the weekends, since we do not work at all during Saturday and Sunday. Using the same logic to isolate the weekends, we subtract 16 for every weekend that we have. This is because we have already subtracted the non-working hours for each day, we just need to account for 8 hours on Saturday and 8 hours on Sunday that we are not working.

2.Drag OrderID,Order Date and Ship Date into Rows.
3.Right-click on the Order Date&Ship Date on the Rows shelf. Select Exact Date and make it Discrete.



4.Drag “Work Hours” and drop into Columns Shelf.



Thanks Guru's,
Prazval.ks

DAX Time Intelligence Functions

   Thanks Guru's, Prazval.Ks