summing values for last date by group

[petercummins]

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?

[Anonymous]

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]

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 · Accepted solution]

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

Owen you sir have made my day!

Perfect simple solution, tested them both thanks!

[ankitpatira]

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