Cumulative rate same month

[AlexJim]

Hello

I'm trying to have a measure that results in the cumulative rate up to the specific year, considering the same months of prior years, as shown below. 

I found a solution like this but it works for years and don't know how to build this for same month periods

=EXP(
     SUMX(FILTER(ALL(Rates), Rates[Year]<=MAX(Rates[Year])),
          LN(1 + Rates[Rate])))

I'd appreciate if anyone can help me on this.

Thanks!

Alex

[tamerj1]

Hi @AlexJim 
Please refer to the attached sample file with the solution 

Comulative Rate = 
VAR CurrentDate = 
    MAX ( Rates[Date] )
VAR CurrentMonthTable =
    FILTER ( ALLSELECTED ( Rates ), MONTH ( Rates[Date] ) = MONTH ( CurrentDate ) )
VAR TableOnAndBefore = 
    FILTER ( CurrentMonthTable, Rates[Date] <= CurrentDate )
RETURN
    PRODUCTX ( TableOnAndBefore, 1 + Rates[Rate] )

[AlexJim]

Very helpful, thanks. I realise I need to split by product as well, but does not seem to filter properly. Sorry for my low level of DAX knowledge.

Thanks

Alex

[v-chenwuz-msft · Accepted solution]

Hi @AlexJim ,

Please try this measure:

Measure =
VAR _m =
    MAX ( 'Table'[Date] )
VAR _f =
    FILTER (
        ALLSELECTED ( 'Table' ),
        YEAR ( [Date] ) = YEAR ( _m )
            && MONTH ( [Date] ) = MONTH ( _m )
            && [Date] <= _m
            && [Product] = MAX ( 'Table'[Product] )
    )
RETURN
    PRODUCTX ( _f, [Rate] + 1 )

Result:

Pbix in the end you can refer.

Best Regards

Community Support Team _ chenwu zhu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.