Forum Discussion

AlexJim's avatar
AlexJim
New Member
4 years ago
Solved

Cumulative rate same month

 

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

 

Cumulative ratesame month
  • v-chenwuz-msft's avatar
    v-chenwuz-msft
    4 years ago

    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.

3 Replies

  • tamerj1's avatar
    tamerj1
    Icon for Community Champion rankCommunity Champion
    4 years ago

    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's avatar
      AlexJim
      New Member
      4 years ago

      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's avatar
        v-chenwuz-msft
        Icon for Community Support rankCommunity Support
        4 years ago

        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.