Running total values include in blank rows

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-bd52912a5bd4
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-Last-Week/ba-p/1051123
https://www.youtube.com/watch?v=pnAesWxYgJ8

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

Forecast Table

Contract Table

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 🙂