Running totals by week (lifetime)

[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?

[v-yingjl · Accepted solution]

Hi @jmillar ,

To calculate the running total, the source table needs a date column and create relationships with your Calendar table based on this case. 

If the source table does not have a date column, the measure would just only calculate the sum value of all the source table fields.

Best Regards,
Community Support Team _ Yingjie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[wdx223_Daniel]

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

RETURN

CALCULATE (

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

'Calendar'[Date] <= MaxDate -- Where date is before the last visible date

)