Cumulative Dax expression for multiple dimension - Product and Year-Month

[sshantaram]

Hello All,

I want to achieve cumulative growth of Product with respective to Year-Month. I am able to achieve it with respect to Year-Month but when I try to achieve it based on other dimension like Product, my logic fails. I am sharing a simple example in the screenshot

Kindly let me know if there is a way to achieve this, also will the logic apply to Product-->Sub-Product-->Year-Month

Thank you for your time

Warm Regards

SK

[v-angzheng-msft · Accepted solution]

Hi, @sshantaram 

To create a measure like this:

Measure =
CALCULATE (
    SUM ( 'Table'[Sales] ),
    FILTER (
        ALLEXCEPT ( 'Table', 'Table'[Product] ),
        'Table'[Date] <= MAX ( 'Table'[Date] )
    )
)

Result:

Please refer to the attachment below for details

Hope this helps.

Best Regards,
Community Support Team _ Zeon Zheng
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[sshantaram]

@v-angzheng-msft Thank you it works. I learnt something new 🙂

[amitchandak]

@sshantaram , first you need to unpivot the year data.  That will give value column

https://radacad.com/pivot-and-unpivot-with-power-bi

You need to have a year table 

Create a measure like one below and use in matrix with year and product

calculate(sum(Table[Value]) , filter(allselected(year), Year[Year] <=  Max(Year[Year])))

[sshantaram]

@amitchandak Thank you for your reply. I have used this calculated measure before "calculate(sum(Table[Value]) , filter(allselected(year), Year[Year] <=  Max(Year[Year])))" but it only works when you have only Time Dim .. but if you add a Product Dim along with Time Dim, we wont get the result. 

Kindly help.

Warm Regards,

SK