Forum Discussion
Show target reduction
rsbin ,
Thank you so much for getting me pointed in the right direction, I wouldn't have been able to accomplish this task without your support.
I apologize it took me a little longer than anticipated but I have finally been able to put together something that accomplishes what I was trying to do:
To get this to calculate the reduction properly I had to use the following:zTestProjectedReduction = VAR minDate = CALCULATE(MIN('table'[weekEndingExpire]),ALLSELECTED('table'[weekEndingExpire])) VAR maxDate = MAX('table'[weekEndingExpire]) VAR noWeeks = DATEDIFF(minDate, maxDate, WEEK) + 1 VAR noEmps = SELECTEDVALUE('table2'[Value]) VAR compTar = SELECTEDVALUE('table3'[Value]) Return CALCULATE( [zProjectedExpTotSum] - ((noEmps * compTar) * noWeeks), 'table'[weekEndingExpire] <=maxDate )The difference between my first attempt and the second is I had to "calculate" the minDate because the previous declaration for that variable:
VAR minDate = MIN('table'[weekEndingExpire])would basically default the value to 1 for every week and not take into consideration previous weeks in the date range.
I also ended up created two new tables ("table2" & "table3") that essentially allow the user of the report to select the number of employees and a completion target. This in turn allows the projection the the bar graph to update dynamically based on the user selection.
Anonymous ,
Good Morning. OK, have a better understanding how your fact table is set up. In theory, we should be able to create a Measure to calculate your Cumulative Reduction. I'm stymied at the moment as to why your Measure doesn't work the way we want it to. Now unfortunately, I'm pretty jammed up today, but have an idea that could get you over the hump for the time being. Let's create a Summary Table with the following columns:
1) Group by Week Ending Expire
2) zProjectedExpTotSum = SUM('table'[zProjectedExpTot]
3) Then in here you can put your [Reduction] = 10
4 & 5) Optional: You can add in your Cumulative Measures as columns so you can track the calculations.
(Lots of info on Summarize or SummarizeColumns - refer SQLBI or Enterprise DNA)
Realize this is an extra step, but this is what I do when I can't immediately resolve an issue. Go back to it when I have more time to study.
Hope this helps and sorry I can't spend time on this today.
Best Regards,
rsbin ,
Thank you so much for getting me pointed in the right direction, I wouldn't have been able to accomplish this task without your support.
I apologize it took me a little longer than anticipated but I have finally been able to put together something that accomplishes what I was trying to do:
To get this to calculate the reduction properly I had to use the following:
zTestProjectedReduction =
VAR minDate = CALCULATE(MIN('table'[weekEndingExpire]),ALLSELECTED('table'[weekEndingExpire]))
VAR maxDate = MAX('table'[weekEndingExpire])
VAR noWeeks = DATEDIFF(minDate, maxDate, WEEK) + 1
VAR noEmps = SELECTEDVALUE('table2'[Value])
VAR compTar = SELECTEDVALUE('table3'[Value])
Return
CALCULATE(
[zProjectedExpTotSum] - ((noEmps * compTar) * noWeeks),
'table'[weekEndingExpire] <=maxDate
)The difference between my first attempt and the second is I had to "calculate" the minDate because the previous declaration for that variable:
VAR minDate = MIN('table'[weekEndingExpire])would basically default the value to 1 for every week and not take into consideration previous weeks in the date range.
I also ended up created two new tables ("table2" & "table3") that essentially allow the user of the report to select the number of employees and a completion target. This in turn allows the projection the the bar graph to update dynamically based on the user selection.