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.
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,
rsbin ,
Totally makes sense. I think the issue I have is that my table has multiple dates and I am calculating the week ending (in a calculated column)
weekEndingExpire = 'table'[Current Expire Date] - MOD('table'[Current Expire Date] -2,7) + 6as well as the number of expirations (using a measure [zProjectedExpTot]) so I don't believe I can add a column to the table called [reduction] b/c of the multiple week ending dates:
ASSET Current Expire Date weekEndingExpire zProjectedExpTot 4029925489 8/29/2022 9/4/2022 1 4021644523 9/2/2022 9/4/2022 1 4020901997 9/2/2022 9/4/2022 1 4029421387 9/4/2022 9/4/2022 1 4020718185 9/4/2022 9/4/2022 1 4021919850 9/9/2022 9/11/2022 1 4021646129 9/9/2022 9/11/2022 1 4007725132 9/9/2022 9/11/2022 1 4021659487 9/10/2022 9/11/2022 1 4021637911 9/10/2022 9/11/2022 1 4020216487 9/10/2022 9/11/2022 1 4029222275 9/10/2022 9/11/2022 1 4021717207 9/11/2022 9/11/2022 1 4021833466 9/18/2022 9/18/2022 1 4021833456 9/18/2022 9/18/2022 1 4029028801 9/20/2022 9/25/2022 1 4029028797 9/20/2022 9/25/2022 1 4021921135 9/24/2022 9/25/2022 1 4021746692 9/24/2022 9/25/2022 1 4020482584 9/24/2022 9/25/2022 1 4021402615 9/25/2022 9/25/2022 1 4021647882 9/30/2022 10/2/2022 1 1006937792 10/1/2022 10/2/2022 1 4021338023 10/2/2022 10/2/2022 1 4021402528 10/4/2022 10/9/2022 1 4007538912 10/8/2022 10/9/2022 1 4020723741 10/8/2022 10/9/2022 1 zProjectedExpTot is a calculated column that returns a 1 or 0 (zero) based on certain criteria. In my visual, I graph the cumulative total by week ending using cumulative formula:
zProjectedExpTotCum = VAR minDate = MIN('table'[weekEndingExpire]) VAR maxDate = MAX('table'[weekEndingExpire]) VAR tDate = TODAY() Return CALCULATE( [zProjectedExpTotSum], 'table'[weekEndingExpire] <= maxDate )where [zProjectedExpTotSum] is:
zProjectedExpTotSum = SUM('table'[zProjectedExpTot])Do you know if it's possible to create a measure to calculate the reduction? That is what I was attempting to put together with this based off of your previous input:
zTestProjectedReduction = VAR minDate = MIN('table'[weekEndingExpire]) VAR maxDate = MAX('table'[weekEndingExpire]) VAR noWeeks = DATEDIFF(minDate, maxDate, WEEK) Return CALCULATE( [zProjectedExpTotCum] - (10 * noWeeks))but that didn't actually graph a reduced amount week over week.