Measure to calculate monthly savings in the future

[Anonymous]

Dear all,

i am looking for a measure to calculate the monthly savings of different actitivites as of now as well as in the future.

The data structure is looking like this:

ID      Activity      monthly savings         Start Date            End Date

1	ABC	1.000,00 €	01.01.2021	31.12.2023
2	DEF	5.000,00 €	01.03.2021	31.12.2023
3	GHI	2.000,00 €	01.05.2021	31.12.2023
4	JKL	4.000,00 €	01.07.2021	31.12.2023
5	MNO	2.000,00 €	01.08.2021	31.12.2023

The measure should calculate the monthly savings continous like this example:

	ID = 1	ID = 2	SUM
Jan 21	1000		1000
Feb 21	1000*2		2000
Mrz 21	1000*3	5000	8000
Apr 21	1000*4	5000*2	14000
May 21	…	…	…

The measure should be used to create a graph showing an ascending curve of savings over time...

It would be great if anyone could support on this! 🙂

Thanks in advance and have a nice day!

[Jihwan_Kim · Accepted solution]

Hi, @Anonymous 

Please check the below picture and the sample pbix file's link down below, whether it is what you are looking for.

All measures are in the sample pbix file.

https://www.dropbox.com/s/sevwxoyzxj29npk/maka.pbix?dl=0 

Hi, My name is Jihwan Kim.

If this post helps, then please consider accept it as the solution to help other members find it faster, and give a big thumbs up.

Linkedin: https://www.linkedin.com/in/jihwankim1975/

[Jihwan_Kim · Accepted solution]

Hi, @Anonymous 

Please check the below picture and the sample pbix file's link down below, whether it is what you are looking for.

All measures are in the sample pbix file.

https://www.dropbox.com/s/sevwxoyzxj29npk/maka.pbix?dl=0 

Hi, My name is Jihwan Kim.

If this post helps, then please consider accept it as the solution to help other members find it faster, and give a big thumbs up.

Linkedin: https://www.linkedin.com/in/jihwankim1975/

[Anonymous]

Just noticed that I need to do minor adjustments on  your given measure. 

The measure should only start to count and cumulate the saving in / after the starting month.

For example, the value for ID = 5 should be "2000" in Aug 2021...

How should I realize that? 

[Jihwan_Kim · Accepted solution]

Hi, @Anonymous 

Terribly sorry about missing the start date.

please try the below.

Monthly Savings cumulate =
VAR startdate =
MAX ( Data[Start Date] )
RETURN
COALESCE (
CALCULATE (
[Monthly Savings],
FILTER (
ALLSELECTED ( 'Calendar'[Date] ),
'Calendar'[Date] <= MAX ( 'Calendar'[Date] )
&& 'Calendar'[Date] >= startdate
)
),
0
)

Hi, My name is Jihwan Kim.

If this post helps, then please consider accept it as the solution to help other members find it faster, and give a big thumbs up.

Linkedin: https://www.linkedin.com/in/jihwankim1975/

[Anonymous]

Hi,  it now calculates exactly as desired. Thanks a lot! 👍

[Anonymous]

Exactly what I was looking for. Thanks a lot @Jihwan_Kim !

@amitchandak Thank you as well, however your blog did not exactly reflect whatI was looking for

[amitchandak]

@Anonymous , refer to my blog for one approach

https://community.powerbi.com/t5/Community-Blog/How-to-divide-distribute-values-between-start-date-or-end-date/ba-p/1503785

and file for the second approach