Forum Discussion
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...
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] ) ) )
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
Super User
Super User
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] ) )
)Owen you sir have made my day!
Perfect simple solution, tested them both thanks!