Cumulative totals by week

[jmillar]

I have a transaction table joined to a date table.  The date table has a calculated "Week Start" column.  I need a running total of Qty, by week, from the beginning of time.  It's a mini-MRP solution, and I'm looking at inventory quantities, purchases orders and scheduled job consumption.  I'm using the following measure:

Qty Demand:=VAR MaxDate = MAX ( 'Calendar'[WeekStart] ) -- Saves the last visible date

RETURN

CALCULATE (

SUM(MRP[TransQty]), -- Computes sales amount

FILTER(ALL('Calendar'),'Calendar'[Date] <= MaxDate) -- Where date is before the last visible date

)

However, it just hangs.  Any suggestions?

[amitchandak]

@jmillar , if you need only one total

Qty Demand:=VAR MaxDate = MAXX ( allselected('Calendar'), 'Calendar'[WeekStart] ) -- Saves the last visible date

RETURN

CALCULATE (

SUM(MRP[TransQty]), -- Computes sales amount

FILTER(ALL('Calendar'),'Calendar'[Date] <= MaxDate) -- Where date is before the last visible date

)

Week by week display

Qty Demand:=VAR MaxDate = MAXX ( allselected('Calendar'), 'Calendar'[WeekStart] ) -- Saves the last visible date

RETURN

CALCULATE (

SUM(MRP[TransQty]), -- Computes sales amount

FILTER(ALL('Calendar'),'Calendar'[Date] <= MaxDate

&& 'Calendar'[Date]  <= Max('Calendar'[Date] )) -- Where date is before the last visible date

)