# A CALCULATE SUM DAX question based on latest value

#### rileybloke

##### New Member
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 No Doc Date Item Qty 1 01/02/2014 111 10 1 01/02/2014 112 15 2 01/02/2014 113 20 2 01/02/2014 111 10 4 03/02/2014 111 10 5 05/02/2014 113 10 6 06/02/2014 111 10 7 07/02/2014 112 10 8 08/02/2014 111 10

<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:

#### mozartiano

##### New Member
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?

#### rileybloke

##### New Member
Highest Doc No... yes you are correct.

#### scottsen

##### Well-known Member
I'm confused Can't you tell us the desired "output" from your sample table?

#### rileybloke

##### New Member
Sorry yes...

 Doc Date Total Qty 01/02/2014 30 03/02/2014 10 05/02/2014 10 06/02/2014 10 07/02/2014 10 08/02/2014 10
<colgroup><col width="75" style="width: 56pt; mso-width-source: userset; mso-width-alt: 2742;"> <col width="64" style="width: 48pt;"> <tbody> </tbody>

#### mozartiano

##### New Member
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.

#### rileybloke

##### New Member
Thanks for the reply, im still learning DAX sorry, any chance you can give me what the DAX would look like please?

#### Tianbas

##### Board Regular
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]))

#### rileybloke

##### New Member
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.

 DocNo Latest Doc DocDate Qty DockCode SU_Code BY_Code Customer MSGDate 723 2504295 22/08/2014 1728 31 47201 9322352348 TMUKE 201434 723 2504295 22/08/2014 1656 31 47201 9322352348 TMUKE 201434 723 2504295 22/08/2014 1512 31 47201 9322352348 TMUKE 201434 723 2504295 22/08/2014 1512 31 47201 9322352348 TMUKE 201434 723 2504295 22/08/2014 1512 31 47201 9322352348 TMUKE 201434 723 2504295 22/08/2014 1440 31 47201 9322352348 TMUKE 201434 723 2504295 22/08/2014 1440 31 47201 9322352348 TMUKE 201434 723 2504295 22/08/2014 1512 31 47201 9322352348 TMUKE 201434 723 2504295 22/08/2014 1512 31 47201 9322352348 TMUKE 201434 723 2504295 22/08/2014 1512 31 47201 9322352348 TMUKE 201434 722 2504295 20/08/2014 1728 31 47201 9322352348 TMUKE 201434 722 2504295 20/08/2014 1656 31 47201 9322352348 TMUKE 201434 722 2504295 20/08/2014 1512 31 47201 9322352348 TMUKE 201434 722 2504295 20/08/2014 1512 31 47201 9322352348 TMUKE 201434 722 2504295 20/08/2014 1512 31 47201 9322352348 TMUKE 201434 722 2504295 20/08/2014 1440 31 47201 9322352348 TMUKE 201434 722 2504295 20/08/2014 1440 31 47201 9322352348 TMUKE 201434

<tbody>
</tbody>

Thanks

Last edited:

#### rileybloke

##### New Member
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

1,078,486
Messages
5,340,616
Members
399,387
Latest member
amrita34

### This Week's Hot Topics

• Problem with Radio Button's format control
I am creating an employee evaluation template (a sample is below) Column A is the category Column B, C D, E and F will be ratings (unacceptable...
• Last Display on userform to a Listbox
[CODE=vba] lstdisplay.ColumnCount = 15 lstdisplay.RowSource = "A1:O600000" [/CODE] So when i do this it Displays everything on the sheet i am...
• Rename and move files to a new location
Dear all, I have an excel file with the following information. The actual file name is at column A but i want to rename it using the following...
• Help with True/False Formula
Hello! Am stumped how to fix this formula, in which my result returns 'True', but it should return False. =IF(AG2=True...
• Clear extra characters from a provided range of cells
Dear All, I have following code which gives me desired output to remove extra characters from a provided range. But it takes too much time when...
• Help with Current and highest streaks
Hi there, I've just joined the forum and this is my first post. I've already spent quite a bit of time searching the net and this forum for a...