DAX Measure - Aggregate values and then sum up results

[dpearson_soco]

I have a Ledger that contains quantities and cost. The quantities will be repeated as the cost of those quantities change. I need to pull the quantity for a given time period of the last time that record was transacted on and then be able to roll up those quantities to the a single row or be able to break them down by various dimensions. 

My model will have the Values of Date, Location, Program, sequence and quantity. 

During a period of time I would want to group by Location and Program and find the highest Sequence number and return the Quantity value. Once I have that I would want to Sum up all those Quantities. I just don't know how to write that as a Measure with DAX. 

[Anonymous · Accepted solution]

Hi @dpearson_soco ,

According to your description, here are my steps you can follow as a solution.

(1) My test data is the same as yours.

(2) You've already created the measure [CurrentQty], let's create a new one.

CurrentQty = 
var MaxSeq = MaxX(f_ActivityLedger, [Sequence])
Var MaxResult = CALCULATE(MaxX(f_ActivityLedger, [Quantity]), FILTER(f_ActivityLedger,f_ActivityLedger[Sequence] = MaxSeq))
Return
MaxResult

//Your measure

New_CurrentQty = 
SUMX(
    SUMMARIZE(
       'f_ActivityLedger',
        'f_ActivityLedger'[Location],
        'f_ActivityLedger'[Program],
        "total",[CurrentQty]
    ),
    [total]
)

 (3) Then the result is as follows.

Best Regards,

Neeko Tang

If this post  helps, then please consider Accept it as the solution  to help the other members find it more quickly. 

[dpearson_soco]

Here is some sample data of two programs at different locations for a single month:

Posting Date	Quantity	Program	Sequence	Location
4/1/2024	5	DVMENV23	45649250	DN
4/1/2024	5	DVMENV23	45920750	DN
4/1/2024	11	DVMENV23	45715050	DN
4/1/2024	12	DVMENV23	45920800	DN
4/1/2024	13	DVMENV23	45693750	DN
4/1/2024	25	DVMENV23	45920300	DN
4/1/2024	26	DVMENV23	45692550	DN
4/1/2024	30	DVMENV23	45649150	DN
4/1/2024	30	DVMENV23	45693850	DN
4/1/2024	34	DVMENV23	45714950	DN
4/1/2024	15	GEPNAV23	45898250	GC
4/1/2024	15	GEPNAV23	45898350	GC
4/1/2024	120	GEPNAV23	45111750	GC
4/1/2024	240	GEPNAV23	45904050	GC
4/1/2024	240	GEPNAV23	45904450	GC
4/1/2024	240	GEPNAV23	45905550	GC
4/1/2024	300	GEPNAV23	45906150	GC

The measure should produce the quantity as below:

Posting Date	Program	Quantity	Location
4/1/2024	DVMENV23	34	DN
4/1/2024	GEPNAV23	300	GC

I would also want the measure to be able to roll up and say I have a quantity of 334 for 4/1/2024

I had written this measure which seems to work when I have the data filtered by program and location, but it won't return the sum of the quantity, it will just give me the quantity for the highest sequenced record in the data set:
CurrentQty:=
var MaxSeq = MaxX(f_ActivityLedger, [Sequence])
Var MaxResult = CALCULATE(MaxX(f_ActivityLedger, [SumCurrentQty]), FILTER(f_ActivityLedger,f_ActivityLedger[Sequence] = MaxSeq))
Return
MaxResult

[SachinNandanwar]

Please post some sample data..It is very difficult to decipher your expalination to come up with a solution for the problem.