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 d...
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] ) )
)
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] ) )
)
    • petercummins's avatar
      petercummins
      Frequent Visitor
      9 years ago

      Owen you sir have made my day!

      Perfect simple solution, tested them both thanks!