Forum Discussion

P567's avatar
P567
Regular Visitor
2 years ago

DAX Calculation - HELP

I have this summarized dataset grouped by multiple variables.

 

This is a screenshot of a table I created from the dataset (other variables are being used as slicers), each index point has mulitple rows in the main dataset
2023 is defined as  CALCULATE(SUM('Append1'[STARTS]), 'Append1'[COMPYEAR] IN { 2023 })

2022 is defined as  CALCULATE(SUM('Append1'[STARTS]), 'Append1'[COMPYEAR] IN { 2022 })

 

I wanted to create two seperate measures each for 2023 and 2022 that will take the previous row value and then add it at each step with the current value at that index point, it should look something like this

 


 

 

 

Calculated Measures
DAX
PBI

7 Replies

  • sjoerdvn's avatar
    sjoerdvn
    Solution Sage
    2 years ago

    You mean a running total?

    RT 2023 = VAR mi=MAX(Append1'[Index]) RETURN CALCULATE(SUM('Append1'[STARTS]), 'Append1'[COMPYEAR] IN { 2023 }, 'Append1'[Index] <= mi)
    • P567's avatar
      P567
      Regular Visitor
      2 years ago

      I should have cleared this ealier but the "Index" col is exactly not an index column. So I have a text field with 7 different values in it and I duplicated that col and just replaced those values with numeric values to able to sort them. And using the formula you provided I see it replicating the values

       

      • sjoerdvn's avatar
        sjoerdvn
        Solution Sage
        2 years ago

        Well, a column is column. What isn't clear from what you've shared so far is what table that column is on, so I assumed it was in the same table. 
        If that "Index" column is on some linked (dimension) table it would explain why the code I sent earlier doesn't work, you would have to change it to something like:

        RT 2023 = VAR mi=MAX(SomeDimensionTable[Index]) RETURN CALCULATE(SUM('Append1'[STARTS]), 'Append1'[COMPYEAR] IN { 2023 }, SomeDimensionTable[Index] <= mi)

         

  • MargusMartsepp's avatar
    MargusMartsepp
    New Member
    2 years ago

    You can create 2 separate running totals:

    RunningTotal_2023 = 
VAR CurrentIndex = MAX('Table'[Index])
RETURN
CALCULATE(
    SUM('Table'[2023]),
    FILTER(
        ALL('Table'),
        'Table'[Index] <= CurrentIndex
    )
)

RunningTotal_2022 = 
VAR CurrentIndex = MAX('Table'[Index])
RETURN
CALCULATE(
    SUM('Table'[2022]),
    FILTER(
        ALL('Table'),
        'Table'[Index] <= CurrentIndex
    )
)

     

    • P567's avatar
      P567
      Regular Visitor
      2 years ago

      I should have cleared this ealier but the "Index" col is exactly not an index column. So I have a text field with 7 different values in it and I duplicated that col and just replaced those values with numeric values to able to sort them