Forum Discussion
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.
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)
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.
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.
- Yes, I just removed the All filter function. Everything works perfectly now. Thanks a lot for your help.
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.
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.