Forum Discussion

GuestUser's avatar
GuestUser
Helper V
6 years ago
Solved

Cumulative dax query help

Hi
We are using power bi with ssas tabular model using connect live option.
Need help in cumulative column
Report format is as below

Matrix view

Year. 2018. 2019
Item. Trxcount. Cumulative Trx...... Cumul
A. 10. 10. 11. 11
B. 20. 30. 22. 33
C. 30. 60. 33. 66

Need help is writing dax query for cumulative measure
Need Help
  • GuestUser's avatar
    GuestUser
    6 years ago

    Hi mwegener 

     

    @Thanks for your inputs!!

    Used below formula(similar)..replaced selected value by hasonevalue and it worked..

     

    Thanks !!

    Measure =
CALCULATE (
    [Sales],
    FILTER (
        ALLSELECTED ( 'Table'[Category] ),
        'Table'[Category] <= SELECTEDVALUE ( 'Table'[Category] )
    )
)

     

23 Replies

  • mwegener's avatar
    mwegener
    Most Valuable Professional
    6 years ago

    Hi GuestUser ,

     

    try this.

     

    Cumulative Trx = 
CALCULATE(
	[Trxcount],
	FILTER(
		ALLSELECTED('Item'[Item]),
		ISONORAFTER('Item'[Item], MAX('Item'[Item]), DESC)
	)
)

     

     

    PBIX

     

    • GuestUser's avatar
      GuestUser
      Helper V
      6 years ago

      Hi mwegener 

       

      While using MAX function , i get the error ...max function can work only on numbers or dates and not string data types

      I am using Connect Live (SSAS Tabular)

       

      just for trying....I checked on pbix file where  import method was used ...it works there...

      Any idea ..why is it so??

       

      Any suggestions on how to implement in connect live?

       

      • mwegener's avatar
        mwegener
        Most Valuable Professional
        6 years ago

        Hi GuestUser ,

         

        I am currently not working with SSAS Tabular, but it is documented in the DAX Guide as follows.

        Returns the largest value in a column, or the larger value between two scalar expressions. Ignores logical values. Strings are compared according to alphabetical order.

        https://dax.guide/max/

         

        Which version are you using?

         

         

  • Mariusz's avatar
    Mariusz
    Community Champion
    6 years ago

    Hi GuestUser 

     

    Try this pattern.

    Cumulative Quantity :=
CALCULATE (
    SUM ( Transactions[Quantity] ),
    FILTER (
        ALL ( 'Date'[Date] ),
        'Date'[Date] <= MAX ( 'Date'[Date] )
    )
)

     Read this article for more info 
    https://www.daxpatterns.com/cumulative-total/

     

    Best Regards,
    Mariusz

    If this post helps, then please consider Accepting it as the solution.

    Please feel free to connect with me.
    LinkedIn



    • GuestUser's avatar
      GuestUser
      Helper V
      6 years ago

      Hi Mariusz 

       

      have tried this and its giving same value for all items same like transaction count

       

      need the cumulative value item wise

       

      any suggestion pls

      • v-eachen-msft's avatar
        v-eachen-msft
        Community Support
        6 years ago

        Hi GuestUser ,

         

        You need to group by Year with ALLEXCEPT() function.

        Refer to the following DAX:

        Measure =
CALCULATE (
    SUM ( 'Table'[Value] ),
    FILTER (
        ALLEXCEPT ( 'Table', 'Table'[Year] ),
        'Table'[Value] <= MAX ( 'Table'[Value] )
    )
)

        Here is the result.