Forum Discussion

DrillDownBI's avatar
DrillDownBI
Icon for Helper I rankHelper I
2 years ago
Solved

Running Total on Measure on a Measure

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 Ac...
Need Help
  • Anonymous's avatar
    Anonymous
    2 years ago

    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's avatar
Greg_Deckler
Icon for Community Champion rankCommunity Champion
2 years ago

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