I have a question and would appreciate support from everyone.
This is my source data:
Order Table: This is a table providing information about sales revenue recorded for each sales invoice on a specific day.
| Date | Reference number | Total sale |
| 1/1/2023 | XK001 | 5000 |
| 1/1/2023 | XK002 | 6000 |
| 1/1/2023 | XK003 | 7000 |
| 1/1/2023 | XK004 | 8000 |
| 2/1/2023 | XK005 | 5000 |
| 2/1/2023 | XK006 | 6000 |
| 3/1/2023 | XK007 | 7000 |
| 4/1/2023 | XK008 | 8000 |
Payment: This table contains information about cash receipt transactions conducted per order on a specific day
| Date | Reference number | Total received amount |
| 1/1/2023 | RC01 | 2000 |
| 1/1/2023 | RC02 | 1000 |
| 1/1/2023 | RC03 | 500 |
| 2/1/2023 | RC01 | 6000 |
| 2/1/2023 | RC02 | 5000 |
This is the report I need to build on Power BI. I need to find a solution to this issue, and I really hope to receive help from everyone
| From 01/01/2023 To 04/01/2023 |
| Date | Reference number | Description | Increase in Incurred | Decrease in Incurred | Closing balance |
| | | Opening balance | | | 30,000 |
| | | Order | 52,000 | 0 | 82,000 |
| 1/1/2023 | RC01 | Payment | 0 | 2000 | 80,000 |
| 1/1/2023 | RC02 | Payment | 0 | 1000 | 79,000 |
| 1/1/2023 | RC03 | Payment | 0 | 500 | 78,500 |
| 2/1/2023 | RC01 | Payment | 0 | 6000 | 72,500 |
| 2/1/2023 | RC02 | Payment | 0 | 5000 | 67,500 |
| | | Total | 52,000 | 14,500 | 67,500 |
Note: The Opening balance is equal to the Closing balance of the previous period (with a date < 01/01/2023)
