Reply
avatar user
Anonymous
Not applicable
Partially syndicated - Outbound

Measure to calculate monthly savings in the future

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

1ABC          1.000,00 €               01.01.2021          31.12.2023
2DEF5.000,00 €01.03.202131.12.2023
3GHI2.000,00 €01.05.202131.12.2023
4JKL4.000,00 €01.07.202131.12.2023
5MNO2.000,00 €01.08.202131.12.2023

 

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

 ID = 1 ID = 2SUM
Jan 21    1000                     1000 
Feb 211000*2 2000
Mrz 211000*350008000
Apr 211000*45000*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!

2 ACCEPTED SOLUTIONS
Jihwan_Kim
Super User
Super User

Syndicated - Outbound

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.

Picture4.png

 

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/

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


Visit my LinkedIn page by clicking here.


Schedule a meeting with me to discuss further by clicking here.

View solution in original post

Syndicated - Outbound

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/

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


Visit my LinkedIn page by clicking here.


Schedule a meeting with me to discuss further by clicking here.

View solution in original post

6 REPLIES 6
Jihwan_Kim
Super User
Super User

Syndicated - Outbound

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.

Picture4.png

 

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/

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


Visit my LinkedIn page by clicking here.


Schedule a meeting with me to discuss further by clicking here.

avatar user
Anonymous
Not applicable

Syndicated - Outbound

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? 

 

maka12_0-1618494198967.png

 

Syndicated - Outbound

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/

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


Visit my LinkedIn page by clicking here.


Schedule a meeting with me to discuss further by clicking here.

avatar user
Anonymous
Not applicable

Syndicated - Outbound

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

avatar user
Anonymous
Not applicable

Syndicated - Outbound

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
Super User
Super User

Syndicated - Outbound

@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

 

Join us as experts from around the world come together to shape the future of data and AI!
At the Microsoft Analytics Community Conference, global leaders and influential voices are stepping up to share their knowledge and help you master the latest in Microsoft Fabric, Copilot, and Purview.
️ November 12th-14th, 2024
 Online Event
Register Here
avatar user

Helpful resources

Announcements
Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!

ArunFabCon

Microsoft Fabric Community Conference 2025

Arun Ulag shares exciting details about the Microsoft Fabric Conference 2025, which will be held in Las Vegas, NV.

December 2024

A Year in Review - December 2024

Find out what content was popular in the Fabric community during 2024.

Top Solution Authors (Last Month)
Top Kudoed Authors (Last Month)