Forum Discussion
Running totals by week (lifetime)
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?
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.
2 Replies
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
)
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.
