Comulative Values with Categories

[vladm]

Hello,

I am trying to build a custom column with comulative values. So far, I accomplished the folllowing:

DATE / PV (Value) / comPV (Comulative Value)

The DAX expression used here is:

comPV =
 CALCULATE(
     sum(TABLE_NAME[PV]);
     ALL(TABLE_NAME);
         TABLE_NAME[DATE] <= EARLIER(TABLE_NAME[DATE])
 )

THE QUESTION: Accomplish the same result but using another column with category.

Now I have:

DATE / CATEGORY / PV (Value) / comPV (Comulative Value) 

01/01/2017 CAT    5   5

01/01/2017 DOG   2   2

02/01/2017 CAT    3   8 (Comulative for CAT up to date 5+3)

02/01/2017 DOG   1  3 (Comulative for DOG up to date 2+1)

I suspect I need to change the sum(TABLE_NAME[PV]) and  EARLIER(TABLE_NAME[DATE]) part and add a category verification.

Thank you in advance,

Vlad M

[v-yulgu-msft · Accepted solution]

Hi @vladm,

Please try this:

comPV =
CALCULATE (
    SUM ( TABLE_NAME[PV] ),
    FILTER (
        TABLE_NAME,
        TABLE_NAME[CATEGORY] = EARLIER ( TABLE_NAME[CATEGORY] )
            && TABLE_NAME[DATE] <= EARLIER ( TABLE_NAME[DATE] )
    )
)

Best regards,
Yuliana Gu