Running Sum based on an existent running sum measure

[fbernardes]

Hello Fabric Community, 

I am stuck with an issue for a couple of days, and haven't been able to figure this out. 

I am trying to create a running sum measure. However, this measure is based on an existent measure that was already created referring to a third measure that is a running sum. - Let me clarify:

• Measure 1: Running Sum Measure
• Measure 2: CALCULATE measure based on Measure 1
• Measure 3: I need to create a running sum of Measure 2. 

I am not being able to get the Measure 3 to be a running sum of Measure 2. I have tried the following: 

**All of the measures below I get the same result: Same values as Measure 2, instead of a running sum.

Measure 3 =

    CALCULATE(

        [Measure 2],

        FILTER(

            ALL('Calendar'[Date]),

            'Calendar'[Date] <= MAX('Calendar'[Date])

        )

    )

 ----------------------------------------------------------------------

Measure 3 =

    TOTALYTD(

        [Measure 2],

            'Calendar'[Date]

    )

----------------------------------------------------------------------

Measure 3 =
VAR CurrentBusinessDate = MAX('Calendar'[Current Business Date])
VAR StartDate = DATE(YEAR(CurrentBusinessDate) - 1, 12, 1)
RETURN
CALCULATE(
[Measure 2],
FILTER(
ALL('Calendar'),
'Calendar'[Date] >= StartDate &&
'Calendar'[Date] <= CurrentBusinessDate
)
)

----------------------------------------------------------------------

Measure 3 =
VAR CurrentDate = MAX('Calendar'[Date])
RETURN
SUMX(
VALUES('Calendar'[Date]),
CALCULATE(
[Measure 2],
'Calendar'[Date] <= CurrentDate
)
)

-----------------------------------------------------------------

Any ideas on what is happening?

Thanks!

[Anonymous]

Hi @fbernardes 

Thanks for the reply from lbendlin .

From the formula you gave, do you need to stack the values ​​of Measure2 for each month? For example, if January is 10 and February is 20, do you need to stack the value of January in February so that it shows 30? I created a sample to test, and in my test, I used a formula similar to your first formula, and it worked. In my test, I only used one table. If the data structure I use is different from yours, then as lbendlin said, you need to provide some sample data so that we can better help you.

Sample:

Measures:

Measure = SUM('Table'[Value])

Measure 2 = CALCULATE([Measure], FILTER(ALL('Table'), 'Table'[Date] <= MAX([Date])))

Output:

Best Regards,
Yulia Xu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[lbendlin]

You cannot measure a measure directly. Either materialize it first, or create a separate measure that implements the entire business logic.

Please provide sample data that covers your issue or question completely, in a usable format (not as a screenshot).

Do not include sensitive information. Do not include anything that is unrelated to the issue or question.

Need help uploading data? https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-...

Please show the expected outcome based on the sample data you provided.

Want faster answers? https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447...