Convert weekly totals by product into a YTD weekly running total by product

[Ramps]

If a data source contains:-

• Product
• Week number
• Weekly qty

How can a table report be produce with YTD qty by product by week number.

Note the date source may not have entries for every week, for example Phones were sold in week 1,2 & 4 but not 3,5 & 6. However, the report requires YTD qtys for each product each week.

I assume a week table, a relationship and a DAX measure or column could be used or convert the week into a date and use the date table. Examples of both methods would be helpful.

I have programmed in other languages but new to DAX. I looked at other posts on the forum and found them confusing becauseI I have not got to grasp with DAX sytntax yet, please can anyone provide a clear examples for a DAX beginner. Thank you.

SOURCE DATA
Product	Week	Weekly Qty
Phone	1	25
Phone	2	17
Phone	4	8
Radio	1	31
Radio	4	34
Radio	6	19
TV	2	14
TV	4	28

DESIRED REPORT
Product	Week	Weekly Qty	YTD Qty
Phone	1	25	25
Phone	2	17	42
Phone	3		42
Phone	4	8	50
Phone	5		50
Phone	6		50
Radio	1	31	31
Radio	2		31
Radio	3		31
Radio	4	34	65
Radio	5		65
Radio	6	19	84
TV	1		0
TV	2	14	14
TV	3		14
TV	4	28	42
TV	5		42
TV	6		42

[v-jiascu-msft]

Hi @Ramps,

Please check out the demo here.

1. A week table full of weeks.

Weeks = GENERATESERIES(1, 52, 1)

2. A product table full of products.

Products = VALUES('SOURCE DATA'[Product])

3. Establish relationships.

4. Create a measure.

YTW =
VAR ytw =
    CALCULATE (
        SUM ( 'SOURCE DATA'[Weekly Qty] ),
        FILTER ( ALL ( Weeks ), 'Weeks'[week] <= MIN ( 'Weeks'[week] ) )
    )
RETURN
    IF ( ISBLANK ( ytw ), 0, ytw )

5. Create a visual and enable "Show data without values".

Best Regards,

Dale

[Ramps]

Many thanks Dale of going to the effort to creating a PBIX example 

It is really helpful and I will use it. Thank you.

This formula is great for viewing in tables but I am still having problems with the line graphs.

See my post about  "Line graph comparing a year's forecast with YTD sales."

https://community.powerbi.com/t5/Desktop/Line-graph-comparing-a-year-s-forecast-with-YTD-sales/m-p/3...

Many thanks

[Greg_Deckler]

RunningTotal = 
VAR MyWeek = MAX(WeeklyTotals[Week])
VAR MySum = CALCULATE(SUM([Weekly Qty]),FILTER(ALLEXCEPT(WeeklyTotals,WeeklyTotals[Product]),[Week]<=MyWeek))
RETURN MySum