Forum Discussion

SErgey-kuznetso's avatar
SErgey-kuznetso
Frequent Visitor
4 years ago
Solved

Cumulative value across two filters

Hi everyone!

 

I have a table of clients with profit on them. Using this table, I need to calculate the cumulative effect of profit by cohort and day since registration within the cohort.

 

Now I use this formula to calculate the cumulative effect:

 

 

cumulative = 
VAR _indx='DBClient'[days_since_register]
RETURN
CALCULATE(SUM(DBClient[gross_profit]),
FILTER(DBClient, DBClient[days_since_register]=_indx)
)

 

 

 

but it does not suit me because it is calculated only within the day, without taking into account the cohort.

 

Please tell me how can I calculate the cumulative effect by cohort and day?

 

Thank you in advance!

 

 

Need Help
  • v-easonf-msft's avatar
    v-easonf-msft
    4 years ago

    Hi， SErgey-kuznetso 

    Modify your formula as below:

     

    cumulative =
VAR _indx = 'DBClient'[days_since_register]
VAR _cohort = 'DBClient'[cohort]
RETURN
    CALCULATE (
        SUM ( DBClient[gross_profit] ),
        FILTER (
            DBClient,
            DBClient[days_since_register] = _indx
                && DBClient[cohort] = _cohort
        )
    )

     

    Best Regards,
    Community Support Team _ Eason

1 Reply

  • v-easonf-msft's avatar
    v-easonf-msft
    Icon for Community Support rankCommunity Support
    4 years ago

    Hi， SErgey-kuznetso 

    Modify your formula as below:

     

    cumulative =
VAR _indx = 'DBClient'[days_since_register]
VAR _cohort = 'DBClient'[cohort]
RETURN
    CALCULATE (
        SUM ( DBClient[gross_profit] ),
        FILTER (
            DBClient,
            DBClient[days_since_register] = _indx
                && DBClient[cohort] = _cohort
        )
    )

     

    Best Regards,
    Community Support Team _ Eason