Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
I am calculating running totals for PO table grouped by week no-YY
Running total PO QTY =
Issue:
W49-23 in PO table qty is blank
W49-23 in Forecast table exist
I need to calculate DEMAND 145 + 110.80 = 255.80
Solved! Go to Solution.
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 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 🙂
@llinasG , I such case always have Date/Week table with all weeks
With date table having yearweek column you can get cumulative like
Cumm Sales = CALCULATE(SUM(Sales[Sales Amount]),filter(all('Date'),'Date'[date] <=max('Date'[date])))
Cumm Sales = CALCULATE(SUM(Sales[Sales Amount]),filter(allselected(date),date[date] <=max(date[Date])))
Cumm Based on Date = CALCULATE([Net], Window(1,ABS,0,REL, ALL('date'[date]),ORDERBY('Date'[date],ASC)))
Cumm Based on Date = CALCULATE([Net], Window(1,ABS,0,REL, ALLSELECTED('date'[date]),ORDERBY('Date'[date],ASC)))
If you have Year week / week table. create a Week Table(Say date) and add week rank column
Have these new columns in Date Table, Week Rank is Important in Date/Week Table
Week Rank = RANKX('Date','Date'[Week Start date],,ASC,Dense)
OR
Week Rank = RANKX('Date','Date'[Year Week],,ASC,Dense) //YYYYWW format
Cumm Week = CALCULATE(sum('Table'[Qty]), FILTER(ALL('Date'),'Date'[Week Rank]<=max('Date'[Week Rank])))
Use week from week or date table in visual
Why Time Intelligence Fails - Powerbi 5 Savior Steps for TI :https://youtu.be/OBf0rjpp5Hw
https://amitchandak.medium.com/power-bi-5-key-points-to-make-time-intelligence-successful-bd52912a5b...
To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s. Refer :radacad sqlbi My Video Series Appreciate your Kudos.
Power BI — Week on Week and WTD
https://medium.com/@amitchandak.1978/power-bi-wtd-questions-time-intelligence-4-5-98c30fab69d3
https://community.powerbi.com/t5/Community-Blog/Week-Is-Not-So-Weak-WTD-Last-WTD-and-This-Week-vs-La...
https://www.youtube.com/watch?v=pnAesWxYgJ8