Running total values include in blank rows

[llinasG]

I am calculating running totals for PO table grouped by week no-YY

Running total PO QTY =

CALCULATE( [SUM of QTY (tonnes)],

FILTER( ALLSELECTED (PO),

PO[Week_No_Year] <= MAX(PO[Week_No_Year])))

Calculating SUM of coverage forcast by week no_YY from a different table

Calculating DEMAND SUM of PO + Forecast

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

[llinasG · Accepted 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.

[llinasG]

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 🙂

[amitchandak]

@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