Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago
Solved

Cumulative totals

Hi, I want to calculate the cumulative values for vol and NNS columns in the screenshot. But also taking into account of the dummy column so rows that have "a" should proceed as 5-10-15 and the ones with "b" should proceed as 6-12-18 for YTD Vol column as an example. Right now my formula doesn't take the Dummy column into account and adds both a and b values for YTD. I would really appreciate If you can help me with a general solution as the screenshot is just me testing this and the real data will have multiple dummy columns and way more than 2 different values in each column.

Need Help
  • Anonymous's avatar
    Anonymous
    7 years ago

    This should work:

    YTD = 
VAR __CurrentDate= Table1[Date]
Var __CurrentDummy = Table1[Dummy]
Var __CurrentYear = YEAR(Table1[Date])

Return
CALCULATE(
    SUM( Table1[vol]),
    FILTER(
        ALL( Table1),
        __CurrentDate >= Table1[Date]
        && __CurrentDummy =  Table1[Dummy]
        && __CurrentYear = YEAR(Table1[Date])
    )
)

  • Anonymous's avatar
    Anonymous
    7 years ago

    Ok I managed to work it, although I just have a single year in my data set so I didnt need to include anything else for other years basically.

3 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    7 years ago

    This should work:

    YTD = 
VAR __CurrentDate= Table1[Date]
Var __CurrentDummy = Table1[Dummy]
Var __CurrentYear = YEAR(Table1[Date])

Return
CALCULATE(
    SUM( Table1[vol]),
    FILTER(
        ALL( Table1),
        __CurrentDate >= Table1[Date]
        && __CurrentDummy =  Table1[Dummy]
        && __CurrentYear = YEAR(Table1[Date])
    )
)

    • Anonymous's avatar
      Anonymous
      Not applicable
      7 years ago

      Thanks for your answer,

      I tried it many different ways but I cant have it work for some reason, Im new to DAX and I cant figure which variable's format I should be changing.

       

  • Anonymous's avatar
    Anonymous
    Not applicable
    7 years ago

    Ok I managed to work it, although I just have a single year in my data set so I didnt need to include anything else for other years basically.