Forum Discussion

vladm's avatar
vladm
New Member
8 years ago
Solved

Comulative Values with Categories

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

  • 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

1 Reply

  • v-yulgu-msft's avatar
    v-yulgu-msft
    Microsoft Employee

    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