Forum Discussion
Helper IIINeed help with representing percentage differences in matrix table
| 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!
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 KongIf this post helps, then please consider Accept it as the solution to help the other members find it more quickly
3 Replies
ryan_mayuSuper User
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
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 KongIf this post helps, then please consider Accept it as the solution to help the other members find it more quickly
- derekli1700
Anonymous 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