Forum Discussion
Running total values include in blank rows
Thanks for the help I managed to resolve it. I changed the source week no to date, removed additional week no table and linked all my data tables to one date table and this seems to solved the issue.
Thanks for reply amitchandak , I added week rank table.
I did not explain my data well in my first post. I will try better now.
I have three tables
PO Table
| PO Date | Target Date | Qty | Plant | Product |
| 05/09/2023 | 05/11/2023 | 1.5 | A | Red |
| 12/09/2023 | 07/11/2023 | 1.2 | B | Blue |
| 01/10/2023 | 11/11/2023 | 2 | A | Green |
| 12/10/2023 | 02/12/2023 | 1.1 | B | Blue |
| 14/10/2023 | 04/12/2023 | 0.8 | B | Blue |
| 01/11/2023 | 06/12/2023 | 9 | B | Red |
| 02/11/2023 | 05/01/2024 | 2 | A | Green |
| 05/11/2023 | 06/01/2024 | 1 | A | Blue |
| 07/11/2023 | 07/01/2024 | 1.3 | B | Red |
Forecast Table
| Week No - Year | Target Date | Qty | Plant | Product |
| 2023-42 | 01/11/2023 | 2 | A | Red |
| 2023-44 | 01/11/2023 | 5 | B | Blue |
| 2023-46 | 01/11/2023 | 6 | A | Green |
| 2023-48 | 01/11/2023 | 7 | B | Blue |
| 2023-42 | 01/12/2023 | 2 | B | Blue |
| 2023-44 | 01/12/2023 | 5 | B | Red |
| 2023-46 | 01/12/2023 | 6 | A | Green |
| 2023-48 | 01/12/2023 | 8 | A | Blue |
| 2023-42 | 01/01/2024 | 9 | B | Red |
| 2023-44 | 01/01/2024 | 3 | A | Blue |
| 2023-46 | 01/01/2024 | 2 | B | Green |
| 2023-48 | 01/01/2024 | 5 | A | Red |
Contract Table
| Contract Date | Target Month | Qty | Plant | Product |
| 05/07/2023 | 01/11/2023 | 5 | A | Red |
| 20/07/2023 | 01/11/2023 | 7 | B | Blue |
| 05/08/2023 | 01/11/2023 | 6 | A | Green |
| 05/09/2023 | 01/12/2023 | 5 | B | Blue |
| 06/09/2023 | 01/12/2023 | 8 | B | Blue |
| 01/10/2023 | 01/01/2024 | 2 | B | Red |
I am looking to create a line chart visual to show:
Y-axis
Demand Forecast = Forecast Qty + Running Total PO Qty
Contracted = Running Total Contract Qty
X-axis
Forecast Week No
Filters By:
* Target Month
* Plant
* Product
To achieve this I created 2 Date Tables and 1 unique week no table
Date Calendar - to link Target Dates and be able to filter by Target Month (NOV-2023 , DEC-2023 , JAN-2023)
Week Calendar - to link (PO Date, Contract Date) to filter by Week No
Distinct Week Calendar - to Link Forecast WeekNo-Year to Week calendar
I calculated running totals Contract Qty and it shows correctly when no filter applied:
When I apply a Target Month Filter it leaves these gaps and the total sum does not include last Qty
I am stuck with this issue for a few days now and very close to giving up 🙂