cancel
Showing results for
Did you mean:
Frequent Visitor

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

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

Warm Regards

SK

1 ACCEPTED SOLUTION
Community Support

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.

4 REPLIES 4
Community Support

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.

Frequent Visitor

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

Super User

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

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])))

Dashboard of My Blogs !! Connect on Linkedin

Learn Power BI Beginners !! Advance Power BI Concepts !! Power BI For Tableau User !! Learn Power BI in Hindi !!
Proud to be a Super User!
Frequent Visitor

@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

Announcements

Launching new user group features

Learn how to create your own user groups today!

Microsoft 365 Collaboration Conference | December 7–9, 2021

Join us, in-person, December 7–9 in Las Vegas, for the largest gathering of the Microsoft community in the world.

Top Solution Authors
Top Kudoed Authors