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

Greg_Deckler
7 years ago
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.

Greg_Deckler

Community Champion

7 years ago

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

Frequent Visitor

7 years ago

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
Community Champion
7 years ago
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
  Frequent Visitor
  7 years ago
  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
    Community Champion
    7 years ago
    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)

Forum Discussion

Days Remaining Calculation

Related content

Days Remaining Calculation

Cases per Day Calculation

Calculated days

Calculating Remaining Day

Remaining Budget Calculation (Days)