Cumulative by multiple criteria and date range

[Paiman]

Hello,

I've stuck on to solve this using dax measure. I have example table called "Crypto", 

Commodity	Recom	Trade_Date	Risk_Month	Value
BTC	Recommended	10/12/2018	1/1/2020	803
ETH	Recommended	11/30/2018	1/1/2020	115
BTC	Recommended	11/28/2018	1/1/2020	750
ETH	Recommended	12/27/2018	1/1/2020	137
ETH	Not Recommend	1/31/2019	1/1/2020	839
ADA	Not Recommend	9/29/2018	2/1/2020	531
ADA	Recommended	10/31/2018	2/1/2020	755
BTC	Not Recommend	12/30/2018	1/1/2020	727
ADA	Not Recommend	11/25/2018	2/1/2020	902
ETH	Recommended	2/28/2019	1/1/2020	148
BTC	Recommended	1/25/2019	1/1/2020	253
ADA	Recommended	12/15/2018	2/1/2020	669

I'd like to make cumulative based on several criterias :
1. column Commodity & Recom are determined using slicer, example : choose BTC & Recommended
2. cumulative grouped only within each risk_month frame, example : BTC cumulative within 1/1/2020, then 2/1/2020, etc.
3. cumulative based on smallest trade date by considering those 2 criterias mentioned above.

Here's my result expectation :

Thanks a lot for the help
Adrian

[Jihwan_Kim · Accepted solution]

Hi, @Paiman 

I am not sure if I understood your question correctly.

I assume you are showing the expected outcome by using different data.

Please check the below.

Cumulative Measure =

CALCULATE (

SUM ( Crypto[Value] ),

FILTER (

ALLEXCEPT ( Crypto, Crypto[Risk_Month], Crypto[Commodity], Crypto[Recom] ),

Crypto[Trade_Date] <= MAX ( Crypto[Trade_Date] )

)

)

https://www.dropbox.com/s/i4klfcoiyffkxiq/paiman.pbix?dl=0 

Hi, My name is Jihwan Kim.

If this post helps, then please consider accept it as the solution to help other members find it faster, and give a big thumbs up.

Linkedin: linkedin.com/in/jihwankim1975/

Twitter: twitter.com/Jihwan_JHKIM

[Paiman]

Thanks a lot, you've helped me so much.. this is the solution i was looking for

[Fowmy]

@Paiman 

Please check the following measure and let me know how it works for you.

Cumm = 
var __tradedate = MAX(Table12[Trade_Date]) return
CALCULATE(
    SUM(Table12[Value]),    
    Table12[Trade_Date] <= __tradedate,
    VALUES(Table12[Risk_Month]),
    ALLSELECTED(table12)
)