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 ,
Based on your explanation, this is what I come up with. Simply create a new Measure:
NewProjectedTotal = [Total] - [Reduction]Then using a Clustered Column Chart and dragging this new Measure, I get this:
Is this what you are looking for?
rsbin ,
Thanks for the quick response, when I created the measure and dropped it into the visual as you suggested I did not get the same result you did. I'm not sure if this is because the measure I have calculating the cumulative total messes it up or if perhaps I didn't implement the measure correctly? Thoughts?
My cumulative total measure is:
My cumulative total measure is:
zProjectedExpTotCum =
VAR minDate = MIN('table'[weekEndingExpire])
VAR maxDate = MAX('table'[weekEndingExpire])
VAR tDate = TODAY()
Return
CALCULATE(
[zProjectedExpTotSum],
'table'[weekEndingExpire] <= maxDate
)
and the measure I created to calculate the reduction is:
zTestProjectedReduction =
[zProjectedExpTotCum] - 10
I'm sure I missed something simple but I'm not sure exactly what... Thanks again for your response, I appreciate any help / guidance you can provide.
Anonymous ,
You are only subtracting 10 from your Total.
You need to subtract 10 * numberofweeks.
Create a [Reduction] measure measure to your [zProjectedExpTotCum]
to obtain a SUM[Reduction].
So what you need is the first week reduction is 10, second week reduction = 20, etc...
This is the table I constructed to get my chart above
DateIncoming Expirations TotalReduction NewProjectedTotal
Sunday, September 11, 2022 416 416 10 406 Sunday, September 18, 2022 420 410 (406+4) 10 410 Sunday, September 25, 2022 426 406 (400+6) 10 416 Sunday, October 2, 2022 430 400 (396+4) 10 420 Sunday, October 9, 2022 433 393 (390+3) 10 423 Sunday, October 16, 2022 448 398 (383+15) 10 438 Hope this provides the additional guidance you require.
rsbin ,
My apologies for the delay in response I was having network issues with my data source so I couldn't attempt to try the proposal. I understand what you mean but I'm not sure I understand how to go about it? I created the following meaure in an attempt to subtract the reduction (multiplied by the number of weeks) but was unsuccessfull. Thoughts on where I went wrong?zTestProjectedReduction = VAR minDate = MIN('table'[weekEndingExpire]) VAR maxDate = MAX('table'[weekEndingExpire]) VAR noWeeks = DATEDIFF(minDate, maxDate, WEEK) Return CALCULATE( [zProjectedExpTotCum] - (10 * noWeeks))Anonymous ,
I created a Cumulative Reduction Measure which assumes you have a column called [Reduction] in your data table (this will allow you the flexibility to modify the amount of Reduction😞
zTestProjectedReductionCum = CALCULATE( SUM(Reduction[Reduction] ), FILTER(ALLSELECTED(Reduction), Reduction[Date] <= MAX(Reduction[Date] )))Not sure why your measure isn't working?? But if you can get to this result then your NewProjectedTotal is:
[zProjectedExpTotCum] - [zTestProjectedReductionCum]Hoping with this, you can get to the result.
Regards,