Running Total on Measure on a Measure

[DrillDownBI]

I have try to calculate a RT where I have Mesures that is already a Measure.
The [Test] Measure is 3 Measures and it shows the correct output on every YearWeek.
[Order_IO_Prod] is where I try to Accumulate the [Test] Value into a Running Total.
First value 42.11 is correct, then should it be 42.11+10.11=52.22 then 52.22 +-14.89=37.33
Instead it takes every row ex 10.11x2=20.22 next -14.89x3= -44.67 next 11.11x4= 44.44 aso

[Anonymous · Accepted solution]

Hi  @DrillDownBI ,

I created some data:

Whether your [YearWeek] is in text format or not, you can create an Index to be used as a sorting sequence for cumulative calculations.

Here are the steps you can follow：

1. Create calculated column.

Rank = 
RANKX(
'Table','Table'[Date],,ASC)

Index =
MAXX(
    FILTER('Table','Table'[YearWeek]=EARLIER('Table'[YearWeek])),[Rank])

2. Create calculated table.

Table 2 =
SUMMARIZE(
    'Table','Table'[YearWeek],'Table'[Index])

3. Joining a relationship between two tables.

4. Show the expression order_IO_Prod in a separate measure.

5. Create measure.

True =
var _today=TODAY()
var _todayindex=MAXX(FILTER(ALL('Table 2'),'Table 2'[YearWeek]=FORMAT(_today,"YYYY-WW")),[Index])
return
SUMX(
    FILTER(ALLSELECTED('Table 2'),
'Table 2'[Index]>_todayindex&&'Table 2'[Index]<=MAX('Table 2'[Index])),[order_IO_Prod])

6. Result:

If it doesn't meet your desired outcome, can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data. We can better understand the problem and help you.

Best Regards,

Liu Yang

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

[Greg_Deckler]

@DrillDownBI Pretty sure that you need to create a virtual table (VAR) that SUMMARIZE by YearWeek and includes your original measure as a column (either as part of the SUMMARIZE or ADDCOLUMNS). Then you can use ADDCOLUMNS to add the running total. You can then return your running total. Something like this:

Order_IO_Prod = 
  VAR __MaxDate = MAX('Calendar'[YearWeek])
  VAR __Table = SUMMARIZE(FILTER('Calendar', [YearWeek] <= __MaxDate), [YearWeek], "__Value", [Original Measure])
  VAR __Table1 = 
    ADDCOLUMNS( 
      __Table, 
      "__RT", 
        VAR __YW = [YearWeek]
        VAR __Return = SUMX( FILTER( __Table, [YearWeek] <= __YW), [__Value] )
      RETURN
        __Return
    )
  VAR __Return = MAXX(FILTER( __Table1, [YearWeek] = __MaxDate), [__RT])
RETURN
  __Return