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