A CALCULATE SUM DAX question based on latest value

rileybloke

New Member
Joined
Feb 7, 2013
Messages
43
Hi All, banging my head against the desk now, so have to ask the pro's... I have a set of data like this...

Doc NoDoc DateItemQty
101/02/201411110
101/02/201411215
201/02/201411320
201/02/201411110
403/02/201411110
505/02/201411310
606/02/201411110
707/02/201411210
808/02/201411110

<tbody>
</tbody>



I need to sum the qty for each Doc No, by date but only the DocNo with the highest number, ignoring any previous docs.
Thanks
 
Last edited:

Excel Facts

Waterfall charts in Excel?
Office 365 customers have access to Waterfall charts since late 2016. They were added to Excel 2019.
What is the "highest number" ? Higuest Qty our Highest Doc no?

If it is higher Doc no, for instance, in the day 01/02/2014 you would only get the quantities for Doc Nos 2? Is that?
 
Upvote 0
Sorry yes...

Doc DateTotal Qty
01/02/201430
03/02/201410
05/02/201410
06/02/201410
07/02/201410
08/02/201410
<colgroup><col width="75" style="width: 56pt; mso-width-source: userset; mso-width-alt: 2742;"> <col width="64" style="width: 48pt;"> <tbody> </tbody>
 
Upvote 0
Well, I can think of a SUMX(TABLE,QTY) where TABLE uses MAX to return a table made of the highest Doc No number by context. Then you put the DATE in the context of the pivot table and it will calculate. Hope this helps.
 
Upvote 0
Thanks for the reply, im still learning DAX sorry, any chance you can give me what the DAX would look like please?
 
Upvote 0
I would go for a 2 step approach

1) add a calculated column to your data table that shows you what Doc No is the highest of the day
LatestDocNo=calculate(max(Table1[Doc No]),filter(Table1,Table1[Doc Date]=earlier(Table1[Doc Date])))

2) add a measure to your pivot that gives you the sum of Qty for all rows with Doc No = LatestDocNo

=calculate(sum(Table[Qty]),Filter(table,table[Doc No] = Table[latestDocNo]))


 
Upvote 0
Thanks Tianbas I tried the Max statement, but because I have other docs for other same customer for the same date it returns the maximum doc for all the docs with that date. Here is the actual table of data im working with including the max statement you gave...

In the below the Latest Doc = 250495 which is another doc sharing the same MSGdate and customer.
We looking for latest doc for MSGDate.


DocNoLatest DocDocDateQtyDockCodeSU_CodeBY_CodeCustomerMSGDate
723250429522/08/2014172831472019322352348TMUKE201434
723250429522/08/2014165631472019322352348TMUKE201434
723250429522/08/2014151231472019322352348TMUKE201434
723250429522/08/2014151231472019322352348TMUKE201434
723250429522/08/2014151231472019322352348TMUKE201434
723250429522/08/2014144031472019322352348TMUKE201434
723250429522/08/2014144031472019322352348TMUKE201434
723250429522/08/2014151231472019322352348TMUKE201434
723250429522/08/2014151231472019322352348TMUKE201434
723250429522/08/2014151231472019322352348TMUKE201434
722250429520/08/2014172831472019322352348TMUKE201434
722250429520/08/2014165631472019322352348TMUKE201434
722250429520/08/2014151231472019322352348TMUKE201434
722250429520/08/2014151231472019322352348TMUKE201434
722250429520/08/2014151231472019322352348TMUKE201434
722250429520/08/2014144031472019322352348TMUKE201434
722250429520/08/2014144031472019322352348TMUKE201434

<tbody>
</tbody>

Thanks :)
 
Last edited:
Upvote 0
Sorry we need to identify the maximum doc no for the MSGDATE, the MSGDATE is YYYYWW format. Therefore I am trying to only include quantities for the highest doc number in my sum. I thought of a count first, but ended up in the same boat :(
 
Upvote 0

Forum statistics

Threads
1,213,496
Messages
6,113,995
Members
448,539
Latest member
alex78

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top