cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
sshantaram
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

 

sshantaram_1-1628089761363.png

 

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

1 ACCEPTED SOLUTION
v-angzheng-msft
Community Support
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:

vangzhengmsft_0-1628227743257.png

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.

View solution in original post

4 REPLIES 4
v-angzheng-msft
Community Support
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:

vangzhengmsft_0-1628227743257.png

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.

View solution in original post

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

amitchandak
Super User
Super User

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

Did I answer your question? Mark my post as a solution! Appreciate your Kudos!! !!
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!

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

 

sshantaram_0-1628148685537.png

 

Kindly help.

 

Warm Regards,

SK

Helpful resources

Announcements
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

November Power BI Update 768x460.png

Check it Out!

Click here to read more about the November 2021 Updates!

M365 768x460.jpg

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.