Days Remaining Calculation

[rohitkalane]

Hello,

I am fairly new to DAX functions and i am trying to calculate a measure which gives me the number of working days remaining for a particular month or upto a particular date. All days except weekends are workdays.

I have 2 tables: an orders table where i have customers, quantity of axles to be shipped and the scheduled date of the shipment. I have a dim date table which i have joined to my orders table on the schedule date.

My main objective is to calculate a measure which will give the average axles to be made per working day, so i am just trying to divide the sum of quantity by the number of distinct working days that fall within a selected time frame.

The problem i face is when a particular customer has some past due scheduled shipment date against him.

e.g. if September 2018 is selected as a filter and Customer A has some quantity (say 50 axles) which were supposed to be shipped on 09/05/2018 and have not yet been shipped which makes it reflect in my orders table as past due, then my WorkingDays measure shows to be blank or zero and this makes my average_axles_per_day measure to be infinity. I guess this happens because when i select September 2018 and Customer A as filters my orders table is filtered to show only the data that matches the condition and it has only 1 scheduled shipment date which is past due.

So how do i get to show the WorkingDays measure to always show only the number of working days which are left in that particular time frame (so as of today 09/17/2018..my num of working days left in September 2018 should be 10). and my sum of past due quantity should be divided by this num of working days (in this case 50 divided by 10).

I appreciate any help provided.

Thank you.