Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Get certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now

Reply
Anonymous
Not applicable

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

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

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

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.

Anonymous
Not applicable

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

 

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.

Anonymous
Not applicable

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

Anonymous
Not applicable

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

@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

Helpful resources

Announcements
November Carousel

Fabric Community Update - November 2024

Find out what's new and trending in the Fabric Community.

Live Sessions with Fabric DB

Be one of the first to start using Fabric Databases

Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.

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! Early Bird pricing ends December 9th.

Nov PBI Update Carousel

Power BI Monthly Update - November 2024

Check out the November 2024 Power BI update to learn about new features.