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.
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?
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)
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.