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