cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
rohitkalane Frequent Visitor
Frequent Visitor

Days Remaining Calculation

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.

1 ACCEPTED SOLUTION

Accepted Solutions
Super User
Super User

Re: Days Remaining Calculation

Oh, that is because of the ALL in that formula. You might try changing that to ALLSELECTED and that might resolve it.

 

Measure 6 = 
VAR __today = TODAY()
VAR __max = MAX('Table14'[Schedule Date])
VAR __total = SUMX(ALLSELECTED(Table14),Table14[New Qty])
VAR __workdays = SUMX(FILTER(ALL('Calendar'),[Date]>=__today && [Date]<=__max),[IsWorkDay])
RETURN DIVIDE(__total,__workdays,0)

Or, you may have to use ALLEXCEPT. Or, you may just use SUMX(Table14,Table14[New Qty]) will depend on exactly what you are trying to do.


Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!


12 REPLIES 12
Super User
Super User

Re: Days Remaining Calculation

I read this over numerous times and am not sure what is going on. Sample data and formulas is always very helpful.

 

Please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

 

All of that being said, I have written a number of work duration quick measures like:

 

https://community.powerbi.com/t5/Quick-Measures-Gallery/Net-Work-Duration/m-p/481543

https://community.powerbi.com/t5/Quick-Measures-Gallery/Net-Work-Days/m-p/367362


Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!


rohitkalane Frequent Visitor
Frequent Visitor

Re: Days Remaining Calculation

@Greg_Deckler Sorry that the post is confusing. I was confused myself about how to post this in a better way. I will try to put some sample data and formula that will help you better understand the issue.

 

Thank you for the references and apologies for the not so well formatted post.

rohitkalane Frequent Visitor
Frequent Visitor

Re: Days Remaining Calculation

Hello @Greg_Deckler

 

So here I am trying to calculate a measure which will give me the number of work days remaining in a selected time frame. This measure will be then used to calculate Average_axles_per day = (Sum of New Qty) / (Num of work days remaining).

 

Weekdays are considered as work days (holidays are ignored)

 

My data has 2 tables:

 

Order ManagementDim Date
CustomerDate Value
Schedule DateFiscal Year
New QuantityFiscal Quarter
 Fiscal Month
 

Fiscal Week

 

I have joined these tables on    Schedule Date * : 1 Date Value        fields.

 

Following is sample data for month of Septemebr 2018:

Customer NameSchedule DateNew Qty
Customer 39/3/20181
Customer 19/3/20186
Customer 79/4/20183
Customer 49/5/20184
Customer 79/5/201849
Customer 79/6/20181
Customer 99/10/20181
Customer 49/10/201826
Customer 79/10/201811
Customer 39/11/20184
Customer 109/11/20185
Customer 79/11/20182
Customer 39/12/20184
Customer 59/12/20182
Customer 79/12/201855
Customer 39/13/201843
Customer 59/13/20187
Customer 29/13/20184
Customer 79/13/201844
Customer 99/14/20181
Customer 39/14/2018137
Customer 89/14/20186
Customer 59/14/201813
Customer 119/14/20182
Customer 19/14/20187
Customer 29/14/2018118
Customer 79/14/201843
Customer 39/15/201812
Customer 99/17/201812
Customer 49/17/201867
Customer 39/17/2018366
Customer 89/17/201844
Customer 59/17/201857
Customer 119/17/20182
Customer 19/17/2018166
Customer 29/17/2018489
Customer 79/17/201844
Customer 69/17/201884
Customer 39/18/2018462
Customer 89/18/201862
Customer 59/18/2018142
Customer 19/18/2018285
Customer 29/18/2018470
Customer 79/18/201829
Customer 69/18/2018281
Customer 99/19/201812
Customer 39/19/2018574
Customer 89/19/201860
Customer 59/19/2018121
Customer 19/19/2018460
Customer 29/19/2018527
Customer 79/19/2018106
Customer 69/19/2018273
Customer 39/20/2018794
Customer 89/20/201849
Customer 59/20/2018122
Customer 19/20/2018391
Customer 29/20/2018515
Customer 79/20/201839
Customer 69/20/2018283
Customer 99/21/201810
Customer 39/21/2018663
Customer 89/21/201849
Customer 59/21/2018131
Customer 119/21/201812
Customer 19/21/2018452
Customer 29/21/2018556
Customer 79/21/201824
Customer 69/21/2018273
Customer 39/22/2018377
Customer 19/22/2018342
Customer 99/24/201848
Customer 49/24/201849
Customer 39/24/2018892
Customer 89/24/201852
Customer 59/24/2018128
Customer 19/24/2018458
Customer 29/24/2018655
Customer 79/24/201880
Customer 69/24/2018277
Customer 99/25/201810
Customer 39/25/2018792
Customer 89/25/201849
Customer 59/25/2018135
Customer 19/25/2018415
Customer 29/25/2018626
Customer 79/25/201824
Customer 69/25/2018270
Customer 99/26/20182
Customer 39/26/2018735
Customer 89/26/201899
Customer 59/26/2018106
Customer 109/26/20181
Customer 19/26/2018376
Customer 29/26/2018570
Customer 79/26/201830
Customer 69/26/2018275
Customer 99/27/201840
Customer 39/27/2018665
Customer 89/27/201853
Customer 59/27/2018126
Customer 119/27/20182
Customer 19/27/2018350
Customer 29/27/2018623
Customer 79/27/201821
Customer 69/27/2018275
Customer 99/28/201858
Customer 39/28/2018688
Customer 89/28/201897
Customer 59/28/201899
Customer 19/28/2018416
Customer 29/28/2018595
Customer 79/28/20183
Customer 69/28/2018277
Customer 39/29/2018421
Customer 19/29/2018345

 

In this case considering today() = 09/18/2018, we can see that there are a few orders which are past due. I would like to calculate a measure which gives me the number of work days which are in between today and the Max Schedule date in the orders list, and then divide the Total sum of New Qty (including past due qty) by those number of work days.

 

In another case where we put a filter on a Customer say Customer 10 without any Fiscal month filter, following is the sample data:

Customer NameSchedule DateNew Qty
Customer 109/11/20185
Customer 109/26/20181
Customer 1010/17/20188
Customer 1010/31/20184

 

In this case 1 of the dates is past due, so i would like to calculate the work days between today and 10/31/2018 (Max Schedule date in the list).

 

In a special case when a customer has only 1 order and that is already past due, then that past due quantity will be divided by the work days remaining in the current month.

 

I hope this information helps to understand the scenario. Please let me know if you would need any additional info.

I appreciate your help.

 

Thank you.

Super User
Super User

Re: Days Remaining Calculation

Sorry, how do we know that an order is past due or is anything in this table with a schedule date less than today past due? Do you already have a column in your calendar table that identifies days as work days or not?


Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!


rohitkalane Frequent Visitor
Frequent Visitor

Re: Days Remaining Calculation

@Greg_Deckler Apologies, I should have mentioned that. Yes any order that has a Schedule date before today is past due.

As far as work day is concerned I created a calculated column which will assign 0 to weekends and 1 to the weekdays and then used it as a flag to identify a workday.

 

IsWorkDay = SWITCH(WEEKDAY([Schedule Date]),1,0,7,0,1)
Super User
Super User

Re: Days Remaining Calculation

So something like this?

 

Measure 6 = 
VAR __today = TODAY()
VAR __max = MAX('Table14'[Schedule Date])
VAR __total = SUMX(ALL(Table14),Table14[New Qty])
VAR __workdays = SUMX(FILTER(ALL('Calendar'),[Date]>=__today && [Date]<=__max),[IsWorkDay])
RETURN DIVIDE(__total,__workdays,0)

Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!


rohitkalane Frequent Visitor
Frequent Visitor

Re: Days Remaining Calculation

@Greg_Deckler 

the part where you calculate the Number of Work days remaining works perfectly, however the Average Axles per day is not being calculated accurately.

 

I guess the problem is that the Total Quantity variable is not changing in value even if i select a particular customer. the total in this measure always remains the Sum of Qty for all customers.

 

 

Thank you.

Super User
Super User

Re: Days Remaining Calculation

Oh, that is because of the ALL in that formula. You might try changing that to ALLSELECTED and that might resolve it.

 

Measure 6 = 
VAR __today = TODAY()
VAR __max = MAX('Table14'[Schedule Date])
VAR __total = SUMX(ALLSELECTED(Table14),Table14[New Qty])
VAR __workdays = SUMX(FILTER(ALL('Calendar'),[Date]>=__today && [Date]<=__max),[IsWorkDay])
RETURN DIVIDE(__total,__workdays,0)

Or, you may have to use ALLEXCEPT. Or, you may just use SUMX(Table14,Table14[New Qty]) will depend on exactly what you are trying to do.


Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!


rohitkalane Frequent Visitor
Frequent Visitor

Re: Days Remaining Calculation

Yes, I just removed the All filter function. Everything works perfectly now. Thanks a lot for your help.