Problem with Running Total Sum and Weeks where input = 0

[Jdidrik]

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. 

[Jdidrik]

Thanks for trying to help me @isjoycewang ! 
Your solution does not work either.  I have made a sample .pbix to show the issue, but I dont understand how to upload it. I get "The file type (.pbix) is not supported." when I try to drag & drop.

In the report the upper graph shows total number of rev each week, and the bottom the running total sum for 6weeks. If we take week 6 as an example. The Rolling sum should be: 7+9+8+6+5+5 = 40. But the "total running sum measure" calculates 36.

Since I am unable to upload the file, below is the table from my sample file:

Week	Assigned Engineer	rev	org
1	Engineer 1	1
1	Engineer 2	1
1	Engineer 3	1
1	Engineer 4	1
1	Engineer 10	0	1
1	Engineer 10	1
2	Engineer 1	0
2	Engineer 1	0	1
2	Engineer 1	1
2	Engineer 5	0	1
2	Engineer 2	0	1
2	Engineer 2	1
2	Engineer 7	0	1
2	Engineer 6	0	1
2	Engineer 6	1
2	Engineer 3	1
2	Engineer 4	0
2	Engineer 4	0	1
2	Engineer 8	0	1
2	Engineer 9	0	1
2	Engineer 10	0	1
2	Engineer 10	1
3	Engineer 1	0	1
3	Engineer 1	1
3	Engineer 5	0
3	Engineer 2	0	1
3	Engineer 2	1
3	Engineer 7	1
3	Engineer 6	0	1
3	Engineer 6	1
3	Engineer 8	0	1
3	Engineer 9	1
3	Engineer 10	0	1
3	Engineer 10	1
4	Engineer 1	0
4	Engineer 1	0	1
4	Engineer 1	1
4	Engineer 5	0	1
4	Engineer 5	1
4	Engineer 2	0	1
4	Engineer 2	1
4	Engineer 7	0	1
4	Engineer 6	0	1
4	Engineer 6	1
4	Engineer 3	1
4	Engineer 4	0	1
4	Engineer 4	1
4	Engineer 8	0	1
4	Engineer 8	1
4	Engineer 10	0
4	Engineer 10	0	1
4	Engineer 10	1
5	Engineer 1	0	1
5	Engineer 1	1
5	Engineer 5	0	1
5	Engineer 5	1
5	Engineer 2	0	1
5	Engineer 2	1
5	Engineer 7	1
5	Engineer 6	0	1
5	Engineer 6	1
5	Engineer 3	1
5	Engineer 4	0	1
5	Engineer 4	1
5	Engineer 8	0	1
5	Engineer 8	1
5	Engineer 10	0
5	Engineer 10	0	1
5	Engineer 10	1
6	Engineer 1	0	1
6	Engineer 1	1
6	Engineer 2	0	1
6	Engineer 2	1
6	Engineer 6	0	1
6	Engineer 6	1
6	Engineer 3	1
6	Engineer 4	0	1
6	Engineer 4	1
6	Engineer 8	0	1
6	Engineer 8	1
6	Engineer 9	0	1
6	Engineer 10	0
6	Engineer 10	0	1
6	Engineer 10	1
7	Engineer 1	0	1
7	Engineer 1	1
7	Engineer 5	0	1
7	Engineer 5	1
7	Engineer 2	0	1
7	Engineer 2	1
7	Engineer 7	0	1
7	Engineer 6	0	1
7	Engineer 6	1
7	Engineer 4	0	1
7	Engineer 4	1
7	Engineer 8	0	1
7	Engineer 8	1
7	Engineer 10	0	1
7	Engineer 10	1
8	Engineer 1	1
8	Engineer 5	0	1
8	Engineer 5	1
8	Engineer 2	0	1
8	Engineer 2	1
8	Engineer 7	0	1
8	Engineer 6	1
8	Engineer 3	0	1
8	Engineer 4	0	1
8	Engineer 4	1
8	Engineer 8	0	1
8	Engineer 8	1
8	Engineer 10	0	1
8	Engineer 10	1
9	Engineer 1	0	1
9	Engineer 1	1
9	Engineer 5	0	1
9	Engineer 5	1
9	Engineer 2	0	1
9	Engineer 2	1
9	Engineer 6	0	1
9	Engineer 6	1
9	Engineer 3	1
9	Engineer 4	0	1
9	Engineer 4	1
9	Engineer 8	0	1
9	Engineer 8	1
9	Engineer 10	0	1
9	Engineer 10	1
10	Engineer 1	0	1
10	Engineer 1	1
10	Engineer 5	0	1
10	Engineer 5	1
10	Engineer 2	0	1
10	Engineer 2	1
10	Engineer 7	0	1
10	Engineer 7	1
10	Engineer 6	0	1
10	Engineer 6	1
10	Engineer 4	0	1
10	Engineer 4	1
10	Engineer 8	0	1
10	Engineer 8	1
10	Engineer 9	1
10	Engineer 10	0	1
10	Engineer 10	1
11	Engineer 1	0
11	Engineer 1	0	1
11	Engineer 1	1
11	Engineer 5	0	1
11	Engineer 5	1
11	Engineer 2	0	1
11	Engineer 2	1
11	Engineer 6	0	1
11	Engineer 6	1
11	Engineer 3	1
11	Engineer 4	0	1
11	Engineer 4	1
11	Engineer 8	0	1
11	Engineer 9	1
11	Engineer 10	0	1
11	Engineer 10	1
12	Engineer 1	0	1
12	Engineer 1	1
12	Engineer 5	0	1
12	Engineer 5	1
12	Engineer 2	0	1
12	Engineer 2	1
12	Engineer 6	0	1
12	Engineer 6	1
12	Engineer 4	0	1
12	Engineer 4	1
12	Engineer 8	0	1
12	Engineer 8	1
12	Engineer 9	0	1
12	Engineer 10	0	1
12	Engineer 10	1
13	Engineer 1	0
13	Engineer 1	0	1
13	Engineer 1	1
13	Engineer 5	0	1
13	Engineer 5	1
13	Engineer 2	1
13	Engineer 6	0
13	Engineer 6	0	1
13	Engineer 6	1
13	Engineer 4	0	1
13	Engineer 4	1
13	Engineer 8	0	1
13	Engineer 8	1
13	Engineer 9	0
13	Engineer 10	0	1
13	Engineer 10	1
14	Engineer 1	0	1
14	Engineer 1	1
14	Engineer 5	1
14	Engineer 2	0	1
14	Engineer 2	1
14	Engineer 6	0	1
14	Engineer 6	1
14	Engineer 4	1
14	Engineer 8	0	1
14	Engineer 8	1
14	Engineer 9	1
14	Engineer 10	0	1
14	Engineer 10	1
15	Engineer 1	0	1
15	Engineer 2	0	1
15	Engineer 6	0	1
15	Engineer 4	0	1
15	Engineer 4	1
15	Engineer 8	0	1
15	Engineer 8	1
15	Engineer 10	0	1
15	Engineer 10	1

Jan Didrik 

[MarkLaf · Accepted solution]

So, the below is similar to what you were trying.

Rolling6WeekTotalRev_BadEngineerFilter = 
VAR _thisWk = MAX( Query1[Week] )
VAR _filt = FILTER( 
    ALL( Query1[Week] ), 
    Query1[Week] > _thisWk-6 
    && Query1[Week] <= _thisWk 
)
RETURN
CALCULATE( SUM( Query1[rev] ), _filt )

This works as you want when Query1[Assigned Engineer] is not used in the visual's fields.

But, it suddenly does not work correctly if we put Query1[Assigned Engineer] in the Legend, as you have highlighted in your posts.

To see what is happening, we can do a simple test. Replace our measure with the following:

Test = 1

This is a simple constant, in any part of the visual where a value can be displayed, it will provide a 1. With this, our stacked columns with Query1[Assigned Engineer] in Legend looks like the below.

It's a little clearer now what the issue is. With Query1[Assigned Engineer] in Legend, it prevents any value from calculating for ( Query1[Week], Query1[Assigned Engineer] ) pairings that do not exist in our table. With the current model, it is impossible to get the result you want.

The answer is that you need to put this into a star schema. Or, at the very least, move Engineer into a separate dimension table. Below are some quick steps to do this if you are not already familiar.

First, create your dimension table. Click Modeling > New Table and do something like the below:

Engineers = ALL( Query1[Assigned Engineer] )

Now, relate your new table to the original:

Your new model should now look something like this.

Next, in your stacked column visual, replace Query1[Assigned Engineer] with Engineers[Assigned Engineer] in the Legend. With this change, the following measure will work. Note it is almost identical except how we calculate the current week at the top.

Rolling6WeekTotalRev = 
VAR _thisWk = CALCULATE( MAX( Query1[Week] ), REMOVEFILTERS( Engineers ) )
VAR _filt = FILTER( 
    ALL( Query1[Week] ), 
    Query1[Week] > _thisWk-6 
    && Query1[Week] <= _thisWk 
)
RETURN
CALCULATE( SUM( Query1[rev] ), _filt )

[Jdidrik]

@MarkLaf , Thanks for your help!

What a simple solution to the issue :S

I must applaud your thorough explanation of the reason for the issue and the solution. This was really insightful for me and helped me understand how Power BI works.

Jan Didrik

Jan Didrik

[isjoycewang]

Hi @Jdidrik,

Does this work as expected for you? It seems the issue is the engineer legend.

Attached the demo file for reference. Let me know if any questions. 

Rolling6WeekTotalRev = 
VAR CurrentWeek =
    MAX ( 'Table'[Week] )
VAR User = 
    VALUES('Table'[Assigned Engineer])
RETURN
    CALCULATE (
        SUMX (
            FILTER (
                ALL ( 'Table' ),
                'Table'[Week] <= CurrentWeek
                    && 'Table'[Week] > CurrentWeek - 6
                    && 'Table'[Assigned Engineer] IN User
            ),
            'Table'[rev]
        )
    )

Best Regards,

Joyce