running sum
1 Topic
Problem with Running Total Sum and Weeks where input = 0
Hi, I have problems with calculating Running sum of number of revisions in a 6 week span. I have in total 10 engineers and I want to see the total number of revisions for rolling 6 weeks. My data is shown in a table like this: (simpified) Assigned Engineer Week Rev New Sales order Engineer 1 1 1 0 xxxxx1 Engineer 2 1 1 0 xxxxx2 Engineer 3 2 0 1 xxxxx3 Engineer 2 2 0 1 xxxxx3 Engineer 1 2 1 0 xxxxx4 I have tried many different Dax expressions, but I keep running into the same issue regardless on solutions I try. With the above table I get the following in return: Total Rollingsum of rev: Week 1 = 2 Week 2 = 2 It should be: Week 1 = 2 Week 2 = 3 My table does not sum up number of Rev per week per engineer, it shows Rev or New = 1 or 0 per order. If one order has multiple revisions in same week, it will have multiple lines with rev = 1. What happenes is that when an Engineer has 0 Rev in a week it doesnt add the rolling 6weeks total Rev for that engineer in the week where he has 0. If that engineer has drev in Week 3, it will calculate the correct total sum. Formula I use (one of the many I have tried): Rolling6WeekTotalRev = VAR CurrentWeek = MAX ( Query1[Week] ) RETURN CALCULATE ( SUMX ( FILTER ( ALL ( Query1 ), Query1[Week] <= CurrentWeek && Query1[Week] > CurrentWeek - 6 ), Query1[rev] ) ) What I really dont undestand, is if I make a separate measure per engineer: Rolling6WeekTotalRev_Engineer2 = VAR CurrentWeek = MAX(Query1[Week]) RETURN CALCULATE( SUMX( FILTER( ALL(Query1), Query1[eCOSAssignedEngineer] = "Engineer2" && Query1[Week] <= CurrentWeek && Query1[Week] > CurrentWeek - 6 ), Query1[rev] ) ) Then it calculates correctly the running 6Weeks total sum for Engineer1: Week 1 = 1 Week 2 = 1 (1+0). I have found workarounds that manage to calculate the correct totalt rolling sum of rev without making separate measures per Engineer, the problem is that as soon as I put in the Engineer as Legend, the calculation fails. (Stacked bar chart). Does anyone have an idea on how I can solve this using one measure? What I want in the end, is a graph that shows the Rolling 6 week average "Rate of Late Change (total Sum Rev / Total Sum New)/Nnumber of weeks. per Engineer. this is easy to solve, if I can mange to get the Rolling Total Rev measure to work correctly.Solved1KViews0likes4Comments