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 ,
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) + 6
as 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.
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 for your time & efforts, I am going to try an figure the measure out through more research here in the forums and other online communities and if I find out what I'm missing/where I've gone wrong I'll be sure to circleback and post my findings.
I can't thank you enough for getting me this far and helping me to learn/better understand the innerworkings of PBI, thank you.
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.