- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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
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!
Solved! Go to Solution.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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/
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.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Hi, @Anonymous
Terribly sorry about missing the start date.
please try the below.
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.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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/
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.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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?
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Hi, @Anonymous
Terribly sorry about missing the start date.
please try the below.
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.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Hi, it now calculates exactly as desired. Thanks a lot! 👍
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
@Anonymous , refer to my blog for one approach
and file for the second approach
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
Join us at the Microsoft Fabric Community Conference
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!
Microsoft Fabric Community Conference 2025
Arun Ulag shares exciting details about the Microsoft Fabric Conference 2025, which will be held in Las Vegas, NV.
Subject | Author | Posted | |
---|---|---|---|
07-04-2024 11:47 AM | |||
Anonymous
| 03-09-2023 03:05 PM | ||
04-03-2024 12:38 AM | |||
Anonymous
| 10-02-2023 12:10 AM | ||
Anonymous
| 10-27-2023 06:59 AM |
User | Count |
---|---|
118 | |
73 | |
56 | |
56 | |
43 |
User | Count |
---|---|
181 | |
121 | |
82 | |
69 | |
54 |