Sum of cumulative inventory / sum of measure

[mikaro]

Hi!

At the moment my matrix looks like this (tammikuu = january):

Column 1 shows the values that my fact table has (Inventory changes).

Column 2 is a cumulative measure of those changes:

2 = CALCULATE (
    SUM ( Fact[value]);
    FILTER (
        ALL (CalendarTable[Date]);
        CalendarTable[Date] <= MAX ( Fact[date] )
    )
)

And column 3 is the problematic one.

Basically I have 2 questions/problems:

1. At the moment every row that doesnt have a cumulative value is shown as 0, but I would like it to be as follows:

1.1.2017	82671	82671
2.1.2017		82671
3.1.2017		82671
4.1.2017		82671
5.1.2017		82671
6.1.2017		82671
7.1.2017		82671
8.1.2017		82671
9.1.2017	-21125	61546
10.1.2017		61546
11.1.2017	-16818	44728
12.1.2017		44728
13.1.2017		44728
14.1.2017		44728
15.1.2017		44728
16.1.2017		44728
17.1.2017		44728
18.1.2017		44728
19.1.2017		44728
20.1.2017		44728
21.1.2017		44728
22.1.2017		44728
23.1.2017		44728
24.1.2017	-44333	395
25.1.2017		395
26.1.2017		395
27.1.2017		395
28.1.2017		395
29.1.2017		395
30.1.2017		395
31.1.2017		395

2. I also need a sum of the third column (in this case 1369084) because in the end I want to calculate the average of the month (1369084/31).

All help and tips are much appreciated!

[Vvelarde]

@mikaro

hi, Point 1:

Running total =
CALCULATE (
    SUM ( 'Table2'[Value] );
    ALLSELECTED ( Table2[Value] );
    FILTER (
        ALLSELECTED ( 'Table2'[Date] );
        ISONORAFTER ( 'Table2'[Date]; MAX ( 'Table2'[Date] ); DESC )
    )
)

 

[mikaro]

Thanks @Vvelarde!

How about the second question? Any ideas on that?

[v-sihou-msft · Accepted solution]

@mikaro

In this scenario, you can add a calculated column to return same result as @Vvelarde's approach.

Column =
CALCULATE (
    SUM ( Table2[value] ),
    FILTER ( Table2, Table2[Date] <= EARLIER ( Table2[Date] ) )
)

Then you can sum those values properly.