Need help with Investment data calculations please!

[RichOB]

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!

[johnt75 · Accepted solution]

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
    Result

The 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"
    )

[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.

[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.

[danextian · Accepted solution]

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.

[johnt75 · Accepted solution]

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
    Result

The 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"
    )

[Akash_Varuna]

Hi @RichOB Could you follow these 

1. Use the existing Rolling_Amount column from Table1 in your matrix.

2. Create a Monthly_Difference measure: CurrentMonth - PreviousMonth using the DATEADD function.

3. Create a Cash_Movement measure: Monthly_Difference - Movement on Investment.

4. Add a matrix visual with rows as Category, columns as months, and values as Rolling_Amount, Monthly_Difference, and Cash_Movement.

[RichOB]

Hi @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