Year over year target by month with static baseline

[brayden1028]

Hi,

I have built a dashboard tracking savings values vs. a 2022 basline.

The problem I am running into is that the savings goal is aimed at saving 10% by 2024. So the goals are 5% savings in 2023 with 10% overall savings as the goal for 2024. I am not very experienced with DAX so this may be a simple solution but I cannot get the 2024 target to basically build off of the 2023 target, therefore making the target cumulative.

As seen below the savings (Y-axis) are cumulative but once the year changes over to 2024 the target (black dashes)is not cumulative and therefore we see the drop in Jan 2024, though the final number in December 2024 is correct as it is what is needed to achieve the 10% by 2024.

Quick sketch of what I am trying to display(ignore target markers seen on previous image):

The current measure used to accomplish target being displayed (Hoping I can build off of this somehow):

Again, not super experienced at DAX so apologies for likely overcomplicating this

Thanks in advance!

[v-tianyich-msft · Accepted solution]

Hi @brayden1028 ,

Please check the attached power query steps before confirming that the results are what you expect.

An attachment for your reference. Hope it helps!

Best regards,
Community Support Team_ Scott Chang

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

[v-tianyich-msft]

Hi @brayden1028 ,

Can you show the sample data and what you want to expect?

Best regards,
Community Support Team_ Scott Chang

[brayden1028]

Hi, 

The issue is the data is somewhat confidential, generally what I am looking to calculate is 24 months accumulating target

This would use the baseline of about 5.5 million, and accumulate to 5% of this by the end of 2023 (275,000) then continue building off of that until we end at the 10% goal by the end of 2024 (550,000)

I could do this with a static Excel sheet but I am trying to do it with Dax as I want to have every location this data is associated with able to filter and look at their specific target. Having the target static from a global level shows progress but removes filterability.

Here is a general overview of what I am looking for listed out:

Month	Target
Jan-23	22,916.66
Feb-23	45,833.32
Mar-23	68,749.98
Apr-23	91,666.64
May-23	114,583.30
Jun-23	137,499.96
Jul-23	160,416.62
Aug-23	183,333.28
Sep-23	206,249.94
Oct-23	229,116.60
Nov-23	252,083.26
Dec-23	274,999.92
Jan-24	297,916.58
Feb-24	320,833.24
Mar-24	343,794.90
Apr-24	366,666.56
May-24	389,583.22
Jun-24	412,499.88
Jul-24	435,416.54
Aug-24	458,333.20
Sep-24	481,249.86
Oct-24	504,166.52
Nov-24	527,083.18
Dec-24	550,000.00

[v-tianyich-msft]

Hi @brayden1028 ,

Sorry for late reply.Is this what you expected?

Measure = DIVIDE(MAX('Table'[ Target ]),5500000)

Hope it helps!

Best regards,
Community Support Team_ Scott Chang

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

[brayden1028]

Hi, No worries!

Sadly I am not sure this will work as the "5,500,000" theoretical number is a sum of data pulled from around 350 buildings all working towards the same 10% target.

Because of this, each plant will have a different amount to save depending on how much they are contriubuting to the 5.5m total. 

So,

One location may have to save 50,000 to reach their 10% target while another location has to save 250,000 to reach their 10% target.

If this is something that cannot be solved by DAX, I may have to take some time and do a manual excel export and calculate the target for each location which is fine, but I was hoping there was a way to do this within PowerBi using DAX.

[brayden1028]

If it helps, I can make this Excel sheet mentioned to display exactly what I would want (using fake location names for confidentiality).

If this is the best way to get this solved, I don't mind taking the time.

[v-tianyich-msft]

Hi @brayden1028 ,

Please provide me with a few simple lines of data and I'll do my best for you.

Best regards,
Community Support Team_ Scott Chang

[brayden1028]

Hi, 

Please see the tables below for some data visualizing what I am trying to show 

The 2022 baseline is what is used to calculate the goals for both years, I am looking to have the 2024 target build off of the 2023 target in my graph as stated before, please refer to the prior picture that I drew on for reference.

2022 Baseline
	Jan-22	Feb-22	Mar-22	Apr-22	May-22	Jun-22	Jul-22	Aug-22	Sep-22	Oct-22	Nov-22	Dec-22
Locations	Consumption	Consumption	Consumption	Consumption	Consumption	Consumption	Consumption	Consumption	Consumption	Consumption	Consumption	Consumption
Location 1	242.04	246.02	231.04	286.53	238.47	232.36	239.78	238.46	206.75	292.34	295.69	280.70
Location 2	16431.29	15530.94	14678.97	13697.69	10978.83	10810.07	9147.63	15769.58	11158.29	12692.50	14661.33	14509.43
Location 3	149.73	147.16	144.42	136.55	144.44	144.10	153.32	142.23	121.98	117.65	113.32	104.81
Location 4	3971.26	4236.97	4065.31	3810.60	2848.92	2660.63	2575.44	2443.59	2976.19	2751.25	2865.87	3260.07
Location 5	29932.12	26556.92	26160.24	20054.48	16139.77	12594.05	12414.34	13834.48	15109.93	19002.12	21914.83	24301.39
TOTALS	50726.44	46718.01	45279.98	37985.85	30350.44	26441.20	24530.51	32428.34	29573.13	34855.87	39851.03	42456.40
2023 Savings needed to achieve 5% Goal
	Jan-23	Feb-23	Mar-23	Apr-23	May-23	Jun-23	Jul-23	Aug-23	Sep-23	Oct-23	Nov-23	Dec-23
Locations	Reduction Goal	Reduction Goal	Reduction Goal	Reduction Goal	Reduction Goal	Reduction Goal	Reduction Goal	Reduction Goal	Reduction Goal	Reduction Goal	Reduction Goal	Reduction Goal
Location 1	12.10	12.30	11.55	14.33	11.92	11.62	11.99	11.92	10.34	14.62	14.78	14.03
Location 2	821.56	776.55	733.95	684.88	548.94	540.50	457.38	788.48	557.91	634.62	733.07	725.47
Location 3	7.49	7.36	7.22	6.83	7.22	7.21	7.67	7.11	6.10	5.88	5.67	5.24
Location 4	198.56	211.85	203.27	190.53	142.45	133.03	128.77	122.18	148.81	137.56	143.29	163.00
Location 5	1496.61	1327.85	1308.01	1002.72	806.99	629.70	620.72	691.72	755.50	950.11	1095.74	1215.07
TOTALS	2536.32	2335.90	2264.00	1899.29	1517.52	1322.06	1226.53	1621.42	1478.66	1742.79	1992.55	2122.82
2023 + 2024 Savings needed to achieve 10% Goal
	Jan-24	Feb-24	Mar-24	Apr-24	May-24	Jun-24	Jul-24	Aug-24	Sep-24	Oct-24	Nov-24	Dec-24
Locations	Reduction Goal	Reduction Goal	Reduction Goal	Reduction Goal	Reduction Goal	Reduction Goal	Reduction Goal	Reduction Goal	Reduction Goal	Reduction Goal	Reduction Goal	Reduction Goal
Location 1	24.20	24.60	23.10	28.65	23.85	23.24	23.98	23.85	20.67	29.23	29.57	28.07
Location 2	1643.13	1553.09	1467.90	1369.77	1097.88	1081.01	914.76	1576.96	1115.83	1269.25	1466.13	1450.94
Location 3	14.97	14.72	14.44	13.66	14.44	14.41	15.33	14.22	12.20	11.77	11.33	10.48
Location 4	397.13	423.70	406.53	381.06	284.89	266.06	257.54	244.36	297.62	275.13	286.59	326.01
Location 5	2993.21	2655.69	2616.02	2005.45	1613.98	1259.40	1241.43	1383.45	1510.99	1900.21	2191.48	2430.14
TOTALS	5072.64	4671.80	4528.00	3798.59	3035.04	2644.12	2453.05	3242.83	2957.31	3485.59	3985.10	4245.64

[v-tianyich-msft · Accepted solution]

Hi @brayden1028 ,

Please check the attached power query steps before confirming that the results are what you expect.

An attachment for your reference. Hope it helps!

Best regards,
Community Support Team_ Scott Chang

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

[brayden1028]

Hi! 

I have had a colleague solve this for me, I do appreciate the time you have taken on this trying to assist.

I can post the solution if that is helpful

[brayden1028 · Accepted solution]

Hi! 

I have had a colleague solve this for me, I do appreciate the time you have taken on this trying to assist.

I can post the solution if that is helpful