Forum Discussion

petercummins's avatar
petercummins
Frequent Visitor
9 years ago
Solved

summing values for last date by group

I need to be able to sum the total quantity of all transactions based on the last transaction date for each product category.
So for a given product category "A" I need to get the last transaction date and then sum all quantities with that last date.

For example the following data should result in two rows returned for each of the two categories: Category A = 2 and Category B = 7


Product, Product Category, Quantity, Date
1, A, 3, 1/1/2015
2, A, 2, 3/3/2015
1, B, 7, 1/1/2015

 

My DAX is as follows:
Last Quantity = CALCULATE(SUM ( Transactions[Quantity]),
FILTER(
ALL(Transactions[Date]) ,
Transactions[Date] = MAX (Transactions[Date])
)
)
My problem is that this formula returns the total quantity of transactions for the last date but does not take into account the product category. How can I get the Last date for the product category?

Need Help
  • OwenAuger's avatar
    OwenAuger
    9 years ago

    petercummins

     

    A couple of options:

     

    =
CALCULATE (
    SUM ( Transactions[Quantity] ),
    GENERATE (
        VALUES ( Transactions[Product Category] ),
        LASTDATE ( Transactions[Date] )
    )
)
    =
SUMX (
    VALUES ( Transactions[Product Category] ),
    CALCULATE ( SUM ( Transactions[Quantity] ), LASTDATE ( Transactions[Date] ) )
)

5 Replies

  • ankitpatira's avatar
    ankitpatira
    Community Champion
    9 years ago

    petercummins In power bi desktop, go to query editor -> right click your  table and duplicate -> then under Transform tab for duplicated table click Group By and apply as below.

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

    Then for duplicated table (Trransactions(2)), click on Merge Queries as shown below. Use control key to select columns for Inner Join.

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

    Finally expand out the Quantity column.

     

  • Anonymous's avatar
    Anonymous
    Not applicable
    9 years ago

    Peter,

    That DAX looks OK to me - if you have a visual like a table or graph with "Product Category" and "Last Quantity", won't it give you what you want?

     

    I notice you say "...should result in two rows returned" - are you looking to get a calculated table back rather than a value?

     

    • petercummins's avatar
      petercummins
      Frequent Visitor
      9 years ago

      Inherantly the problem is that my DAX formula works if I slice by a single product but if all products are selected then it takes the MAX date across all products and returns the sum of quantities for that. What it should do is get the maximum date for the selected period and product and then sum these.

       

      Ankitpatira I tried out your example thankyou. The problem is though that the user may want to look at this by month, week or Year. So if they pick by month then I need the total quantity summed by product for all those transactions on the last transaction date of each monh.

      • OwenAuger's avatar
        OwenAuger
        Super User
        9 years ago

        petercummins

         

        A couple of options:

         

        =
CALCULATE (
    SUM ( Transactions[Quantity] ),
    GENERATE (
        VALUES ( Transactions[Product Category] ),
        LASTDATE ( Transactions[Date] )
    )
)
        =
SUMX (
    VALUES ( Transactions[Product Category] ),
    CALCULATE ( SUM ( Transactions[Quantity] ), LASTDATE ( Transactions[Date] ) )
)