Forum Discussion
Post PartisanNeed help with Investment data calculations please!
I have a bit of a head-scratcher to get to a calculation for investment figures. Using the raw data in Table1, I need to create a Table2 2 in a matrix/table visual. I need:
- I do not have the Rolling_Amount column, that is there to show what I need. I need it to show the Rolling_Amount figure based on the accumulation of the previous month's Amounts.
e.g April to July is 2900, April to August is 3400.
- A Calculation that gives the Monthly_Difference between the monthly Investment amount
e.g May - April = 200. June - May = 1200.
- A calculation to get the Cash Movement, which is the Movement on Investments - Monthly_Difference.
e.g May is 200 - 70. Cash_Movement Total for May is 130
| Table 1 | |||
| Category | Posting_Date | Amount | Rolling_Amount |
| Investments | 30/04/2024 | 600 | |
| Investments | 31/05/2024 | 200 | 800 |
| Investments | 31/06/2024 | 1200 | 2000 |
| Investments | 30/07/2024 | 900 | 2900 |
| Investments | 31/08/2024 | 500 | 3400 |
| Investments | 30/09/2024 | -500 | 2900 |
| Investments | 31/10/2024 | 200 | 3100 |
| Investments | 30/11/2024 | 980 | 4080 |
| Investments | 31/12/2024 | 100 | 4180 |
| Movement on Investment | 30/04/2024 | 60 | |
| Movement on Investment | 31/05/2024 | 70 | |
| Movement on Investment | 31/06/2024 | 45 | |
| Movement on Investment | 30/07/2024 | 30 | |
| Movement on Investment | 31/08/2024 | 60 | |
| Movement on Investment | 30/09/2024 | 65 | |
| Movement on Investment | 31/10/2024 | 40 | |
| Movement on Investment | 30/11/2024 | 20 | |
| Movement on Investment | 31/12/2024 | 10 |
| Table2 | |||||||||
| Category | April | May | June | July | August | September | October | November | December |
| Investments (Rolling_Amount) | 600 | 800 | 2000 | 2900 | 3400 | 2900 | 3100 | 4080 | 4180 |
| Monthly Difference | 200 | 1200 | 900 | 500 | -500 | 200 | 980 | 100 | |
| Movement on Investment | 60 | 70 | 45 | 30 | 60 | 65 | 40 | 20 | 10 |
| Cash Movement | 130 | 1155 | 870 | 440 | -435 | 160 | 960 | 90 |
Thanks in advance for your help!
Make sure you have a date table, marked as a date table, linked to your Posting Date column in a one-to-many single direction relationship.
You can then create measures like
Rolling Amount = VAR MaxDate = MAX ( 'Date'[Date] ) VAR Result = CALCULATE ( SUM ( 'Table'[Amount] ), 'Date'[Date] <= MaxDate, 'Table'[Category] = "Investments" ) RETURN ResultThe monthly difference is just the sum of the amount column
Monthly difference = CALCULATE ( SUM ( 'Table'[Amount] ), 'Table'[Category] = "Investments" )and the cash movement would be
Cash Movement = [Monthly Difference] - CALCULATE ( SUM ( 'Table'[Amount] ), 'Table'[Category] = "Movement on investment" )Hi RichOB
Try this calc column
Rolling Invesments = IF ( 'Table'[Category] = "Investments", CALCULATE ( SUM ( 'Table'[Amount] ), FILTER ( 'Table', 'Table'[Category] = "Investments" && 'Table'[Posting_Date] <= EARLIER ( 'Table'[Posting_Date] ) ) ) )But you can actually do this with measures
What I dont get is for September, your cash movement is -435 when -500-65 = -565
Please see the attached sample pbix for the details.
7 Replies
- Akash_Varuna
Super User
Hi RichOB Could you follow these
Use the existing Rolling_Amount column from Table1 in your matrix.
Create a Monthly_Difference measure: CurrentMonth - PreviousMonth using the DATEADD function.
Create a Cash_Movement measure: Monthly_Difference - Movement on Investment.
Add a matrix visual with rows as Category, columns as months, and values as Rolling_Amount, Monthly_Difference, and Cash_Movement.
RichOBHi Akash_Varuna I don't have an existing Rolling_Amount column from Table 1, that's what I need help with. Your other steps, 2,3 and 4 are doable after the Rolling_Amoutn column is created.
Thanks
johnt75Make sure you have a date table, marked as a date table, linked to your Posting Date column in a one-to-many single direction relationship.
You can then create measures like
Rolling Amount = VAR MaxDate = MAX ( 'Date'[Date] ) VAR Result = CALCULATE ( SUM ( 'Table'[Amount] ), 'Date'[Date] <= MaxDate, 'Table'[Category] = "Investments" ) RETURN ResultThe monthly difference is just the sum of the amount column
Monthly difference = CALCULATE ( SUM ( 'Table'[Amount] ), 'Table'[Category] = "Investments" )and the cash movement would be
Cash Movement = [Monthly Difference] - CALCULATE ( SUM ( 'Table'[Amount] ), 'Table'[Category] = "Movement on investment" )- danextian
Hi RichOB
Try this calc column
Rolling Invesments = IF ( 'Table'[Category] = "Investments", CALCULATE ( SUM ( 'Table'[Amount] ), FILTER ( 'Table', 'Table'[Category] = "Investments" && 'Table'[Posting_Date] <= EARLIER ( 'Table'[Posting_Date] ) ) ) )But you can actually do this with measures
What I dont get is for September, your cash movement is -435 when -500-65 = -565
Please see the attached sample pbix for the details.
v-echaithraCommunity Support
Hi RichOB ,
As we haven’t heard back from you, we wanted to kindly follow up to check if the solution provided for the issue worked? or Let us know if you need any further assistance?
If our response addressed, please mark it as Accept as solution and click Yes if you found it helpful.Regards,
Chaithra.- v-echaithra
Hi RichOB ,
We wanted to kindly follow up to check if the solution provided for the issue worked? or Let us know if you need any further assistance?
If our response addressed, please mark it as Accept as solution and click Yes if you found it helpful.Regards,
Chaithra. - v-echaithra
Hi RichOB ,
As we haven’t heard back from you, we wanted to kindly follow up to check if the solution provided for the issue worked? or Let us know if you need any further assistance?
If our response addressed, please mark it as Accept as solution and click Yes if you found it helpful.Regards,
Chaithra.
