Forum Discussion
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.
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.
12 Replies
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
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.
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 Management Dim Date Customer Date Value Schedule Date Fiscal Year New Quantity Fiscal 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 Name Schedule Date New Qty Customer 3 9/3/2018 1 Customer 1 9/3/2018 6 Customer 7 9/4/2018 3 Customer 4 9/5/2018 4 Customer 7 9/5/2018 49 Customer 7 9/6/2018 1 Customer 9 9/10/2018 1 Customer 4 9/10/2018 26 Customer 7 9/10/2018 11 Customer 3 9/11/2018 4 Customer 10 9/11/2018 5 Customer 7 9/11/2018 2 Customer 3 9/12/2018 4 Customer 5 9/12/2018 2 Customer 7 9/12/2018 55 Customer 3 9/13/2018 43 Customer 5 9/13/2018 7 Customer 2 9/13/2018 4 Customer 7 9/13/2018 44 Customer 9 9/14/2018 1 Customer 3 9/14/2018 137 Customer 8 9/14/2018 6 Customer 5 9/14/2018 13 Customer 11 9/14/2018 2 Customer 1 9/14/2018 7 Customer 2 9/14/2018 118 Customer 7 9/14/2018 43 Customer 3 9/15/2018 12 Customer 9 9/17/2018 12 Customer 4 9/17/2018 67 Customer 3 9/17/2018 366 Customer 8 9/17/2018 44 Customer 5 9/17/2018 57 Customer 11 9/17/2018 2 Customer 1 9/17/2018 166 Customer 2 9/17/2018 489 Customer 7 9/17/2018 44 Customer 6 9/17/2018 84 Customer 3 9/18/2018 462 Customer 8 9/18/2018 62 Customer 5 9/18/2018 142 Customer 1 9/18/2018 285 Customer 2 9/18/2018 470 Customer 7 9/18/2018 29 Customer 6 9/18/2018 281 Customer 9 9/19/2018 12 Customer 3 9/19/2018 574 Customer 8 9/19/2018 60 Customer 5 9/19/2018 121 Customer 1 9/19/2018 460 Customer 2 9/19/2018 527 Customer 7 9/19/2018 106 Customer 6 9/19/2018 273 Customer 3 9/20/2018 794 Customer 8 9/20/2018 49 Customer 5 9/20/2018 122 Customer 1 9/20/2018 391 Customer 2 9/20/2018 515 Customer 7 9/20/2018 39 Customer 6 9/20/2018 283 Customer 9 9/21/2018 10 Customer 3 9/21/2018 663 Customer 8 9/21/2018 49 Customer 5 9/21/2018 131 Customer 11 9/21/2018 12 Customer 1 9/21/2018 452 Customer 2 9/21/2018 556 Customer 7 9/21/2018 24 Customer 6 9/21/2018 273 Customer 3 9/22/2018 377 Customer 1 9/22/2018 342 Customer 9 9/24/2018 48 Customer 4 9/24/2018 49 Customer 3 9/24/2018 892 Customer 8 9/24/2018 52 Customer 5 9/24/2018 128 Customer 1 9/24/2018 458 Customer 2 9/24/2018 655 Customer 7 9/24/2018 80 Customer 6 9/24/2018 277 Customer 9 9/25/2018 10 Customer 3 9/25/2018 792 Customer 8 9/25/2018 49 Customer 5 9/25/2018 135 Customer 1 9/25/2018 415 Customer 2 9/25/2018 626 Customer 7 9/25/2018 24 Customer 6 9/25/2018 270 Customer 9 9/26/2018 2 Customer 3 9/26/2018 735 Customer 8 9/26/2018 99 Customer 5 9/26/2018 106 Customer 10 9/26/2018 1 Customer 1 9/26/2018 376 Customer 2 9/26/2018 570 Customer 7 9/26/2018 30 Customer 6 9/26/2018 275 Customer 9 9/27/2018 40 Customer 3 9/27/2018 665 Customer 8 9/27/2018 53 Customer 5 9/27/2018 126 Customer 11 9/27/2018 2 Customer 1 9/27/2018 350 Customer 2 9/27/2018 623 Customer 7 9/27/2018 21 Customer 6 9/27/2018 275 Customer 9 9/28/2018 58 Customer 3 9/28/2018 688 Customer 8 9/28/2018 97 Customer 5 9/28/2018 99 Customer 1 9/28/2018 416 Customer 2 9/28/2018 595 Customer 7 9/28/2018 3 Customer 6 9/28/2018 277 Customer 3 9/29/2018 421 Customer 1 9/29/2018 345 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 Name Schedule Date New Qty Customer 10 9/11/2018 5 Customer 10 9/26/2018 1 Customer 10 10/17/2018 8 Customer 10 10/31/2018 4 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.
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?
