Need help with representing percentage differences in matrix table

[derekli1700]

Date of Action	Customer	Volume	Transaction	Assets
13/01/2020	Customer A	44,499.2	1,148	60
5/06/2021	Customer A	145,243.4	508	106
26/06/2022	Customer A	758.7	15	2
28/11/2023	Customer A	1,669.1	29	8
25/07/2024	Customer A	15,330.9	287	51
5/03/2020	Customer B	2,186.1	16	1
26/08/2021	Customer B	631.6	16	4
15/08/2023	Customer B	220,562.9	854	63
23/05/2024	Customer B	26,053.9	103	29
5/04/2022	Customer C	1,423.1	32	12
31/03/2023	Customer C	62,440.1	214	58
4/08/2023	Customer C	2,245.7	17	1
6/03/2024	Customer C	42,202.8	58	12

This is a sample of my dataset that im putting into excel. Below is what the matrix table looks like on Power Bi

Is there a way to input columns next to "sum of assets", another next to "sum of transaction" and "sum of volume" that shows the decrease or increase as a percentage over the years? Ideally when we drill down and expand on the months - it will also show the percentage differences between the months too. Thanks!

[v-kongfanf-msft · Accepted solution]

Hi @derekli1700 ,

Maybe you need create a Date table. And then try formula like below:

Assets_Pct_Change =
VAR CurrentYearAssets =
    SUM ( 'FactTable'[Assets] )
VAR PreviousYearAssets =
    CALCULATE ( SUM ( 'FactTable'[Assets] ), DATEADD ( 'Date'[Date], -1, YEAR ) )
RETURN
    DIVIDE ( CurrentYearAssets - PreviousYearAssets, PreviousYearAssets, 0 )

Transaction_Pct_Change =
VAR CurrentYearTransaction =
    SUM ( 'FactTable'[Transaction] )
VAR PreviousYearTransaction =
    CALCULATE (
        SUM ( 'FactTable'[Transaction] ),
        DATEADD ( 'Date'[Date], -1, YEAR )
    )
RETURN
    DIVIDE (
        CurrentYearTransaction - PreviousYearTransaction,
        PreviousYearTransaction,
        0
    )

Volume_Pct_Change =
VAR CurrentYearVolume =
    SUM ( 'FactTable'[Volume] )
VAR PreviousYearVolume =
    CALCULATE ( SUM ( 'FactTable'[Volume] ), DATEADD ( 'Date'[Date], -1, YEAR ) )
RETURN
    DIVIDE ( CurrentYearVolume - PreviousYearVolume, PreviousYearVolume, 0 )

Best Regards,
Adamk Kong

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

[derekli1700]

@v-kongfanf-msft hi - the calculations doesnt seem to work when you drill down into month? It works for YOY difference but no month by month difference? Thanks

[ryan_mayu]

maybe you can try DATEADD function to get next month's value

https://learn.microsoft.com/en-us/dax/dateadd-function-dax?wt.mc_id=DP-MVP-5004616