Hi,
I need some help to calculate cumulative sum in matrix.
I have income matrix by date1 and date2 columns as below.
Date2Month
| Date1Year | Date1Month | February | March | April | May | June |
| 2024 | February | 61 | -23 | 86 | 70 | 13 |
| 2024 | March | | 81 | 70 | 45 | 5 |
| 2024 | April | | | 91 | 59 | 42 |
| 2024 | May | | | | 99 | 78 |
| 2024 | June | | | | | 170 |
And, there is also total cost by date1 as below,
Date2Month Cost
| February | 632 |
| March | 83 |
| April | 128 |
| May | 181 |
| June | 217 |
The matrix I want to reach out is as below.
Date2Month
| Date1Year | Date1Month | February | March | April | May | June |
| 2024 | February | -571 | -594 | -508 | -438 | -424 |
| 2024 | March | | -2 | 68 | 113 | 148 |
| 2024 | April | | | -37 | 22 | 64 |
| 2024 | May | | | | -82 | -4 |
| 2024 | June | | | | | -47 |
And the calculation of aim matrix;
1- Income- Cost for diagonal cells (Feb-Feb, March-March,...)
2- For next months the calculation should be aggregated sum.
Here is how the matrix should be calculated as,
Date2Month
| Date1Year | Date1Month | February | March | April | May | June |
| 2024 | February | "61-632=-571" | "-571-23" | "-594+86" | "-508+70" | "-438+13" |
| 2024 | March | | "81-83" | "-2+70" | "68+45" | "113+35" |
| 2024 | April | | | "91-128" | "-37+59" | "22+42" |
| 2024 | May | | | | "99-181" | "-82+78" |
| 2024 | June | | | | | "170-217" |
I can upload a sample pbix file if you need. Thank you for your helping.
Regards,
Veli
