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 t...
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.

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.