Power BI - DAX for Rolling Sum (Running total) not working,

0
votes

I'm trying to calculate the rolling sum in the MS Power BI application using DAX.

I've got the following table, and adding another column which would display the running total of the [hours] column,

enter image description here

But I'm only getting 1 distinct value to each row (grand total) with the following DAX, 

RollingTotal = 
    calculate (sum('costtrackerdb timesheet'[hours]), 
                 FILTER(ALL('costtrackerdb timesheet'), 
                  'costtrackerdb timesheet'[Week] < 20),
                    values('costtrackerdb timesheet'[Week]))

Where am I going wrong..? I would potentially achieve two things,

  1. Running total hours of the entire rows
  2. Running total hours by each staff
2
powerpivotdata-analysisdaxpowerbi

2 Answers

1
votes

A running total should not be solved with a calculated column but with a calculated measure.

See the following article: http://www.daxpatterns.com/cumulative-total/

In case your Running Total is not date Related then you could implement something like this: https://javierguillen.wordpress.com/2011/12/13/rolling-sum-across-categories-that-are-not-dates/

0
votes

Here I am Calculating Running Inventory (i.e Running total of inventory),I Hope it will help.

Running total = CALCULATE(SUM(Query1[Inventory Quantity]),FILTER(ALLSELECTED(Query1),Query1[DocDate]<=MAX(Query1[DocDate]))

)