Show target reduction

[Anonymous]

Hey BI Community,

Not sure what term(s) to search for to see if someone has already accomplished what I'm looking to do so if there is a potential solution that someone already knows of and can point me in that direction so I can see if I can reconstruct for my purposes, I would be extremely grateful.  Thanks in advance for any pointers/guidance provided.

I have a graph that shows a cumulative expired total by week ending:

What I'd like to accomplish is to create a measure that I can graph right next to each bar to show if we had a reduction of 10 (for easy math) expirations a week, what it'd look like by year end.

Essentially the bars I want to graph would follow the logic in this table:

Date	Incoming Expirations	Total	Reduction	New Projected Total
9/11	416	416	10	406
9/18	4 (420-416)	410 (406+4)	10	400
9/25	6 (426-420)	406 (400+6)	10	396
10/2	4 (430-426)	400 (396+4)	10	390
10/9	3 (433-430)	393 (390+3)	10	383
10/16	15 (448-433)	398 (383+15)	10	388

I apologize if this has been resolved so if anyone knows of an example I could look at or could provide me with some pointers on how to create a calculation to perform what I'm trying to accomplish I would greatly appreciate it.  Thanks again in advance for your time, I appreciate it.

[v-xiaotang]

Hi @Anonymous 

Thanks for reaching out to us.

I just want to confirm if you resolved this issue? If yes, you can accept the answer helpful as the solution or share you method and accept it as solution, thanks for your contribution to improve Power BI.

If you need more help, please let me know.

Best Regards,

Community Support Team _Tang

If this post helps, please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

@v-xiaotang ,

My aplogies on the delayed response, I was working through some network issues and requested further assistance.

[rsbin]

@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?

[Anonymous]

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

[rsbin]

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

[Anonymous]

@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))

[rsbin]

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

[Anonymous]

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

[rsbin]

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

[Anonymous · Accepted solution]

@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]

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