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.

[Greg_Deckler]

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

[rohitkalane]

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.

[Greg_Deckler]

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?

[rohitkalane]

@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)

[Greg_Deckler]

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)

[rohitkalane]

@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.

[Greg_Deckler · Accepted solution]

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.

[rohitkalane]

@Greg_Deckler

I was trying out a scenario as mentioned in my problem statement, where for a particular customer if i only have past due records, then in such a case my total past due quantity should be divided by the number of working days left in the current month.

The measure you provided did not provide me with working days in such a case. It just showed up as blank.

Example:

Region	Customer Name	Schedule Date	New Qty
Mexico	Customer A	9/5/2018 0:00	4
Mexico	Customer A	9/10/2018 0:00	24
Mexico	Customer A	9/17/2018 0:00	67

In this case all my orders for customer A are past due, and since we are in month September, the past due total qty should be divided by working days remaining in September i.e. it must be like (4+24+67) / 6.

The working days left part of the calculation returns blank in this case.

Can you please help?

Thank you.

[Greg_Deckler]

OK, are we working form this version of the formula?

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)

What I am thinking is going on here is that the issue is the __max variable. I think what I was thinking was that the [Schedule Date] table would have the last day of the month in it but I guess that's kind of silly now that I think about it, so perhaps something like:

Measure 6 = 
VAR __today = TODAY()
VAR __maxSchedule = MAX('Table14'[Schedule Date])
VAR __maxMonth = MAX('Table14'[Schedule Date])
VAR __maxYear = MAX('Table14'[Schedule Date])
VAR __max = MAXX(FILTER('Calendar',YEAR([Date])=__maxYear && MONTH([Date])=__maxMonth),[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)

So, basically, get the MAX of the Schedule Date. Figure out the Year and Month for that date. Use those values to figure out the last day of the month from the Calendar table.

[rohitkalane]

@Greg_Deckler

hi, so I tried the latest formula that you provided, but it is not working. It is just giving all my averages as zero, so i guess somethng is not working right in that formula.

The basic idea is to first see the Max Schedule date, if that date is greater than today then calculate the days between that Max Schedule date and today.

Or if

The Max Schedule date is before today, the figure out what the current month is and then calculate the work days left in the current month.

[rohitkalane]

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

[rohitkalane]

@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.