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:

mozartiano

New Member
Joined
Sep 18, 2014
Messages
18
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?
 

scottsen

Well-known Member
Joined
Mar 16, 2014
Messages
1,263
I'm confused :) Can't you tell us the desired "output" from your sample table?
 

rileybloke

New Member
Joined
Feb 7, 2013
Messages
43
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>
 

mozartiano

New Member
Joined
Sep 18, 2014
Messages
18
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
Joined
Feb 7, 2013
Messages
43
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
Joined
Apr 29, 2014
Messages
101
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
Joined
Feb 7, 2013
Messages
43
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:

rileybloke

New Member
Joined
Feb 7, 2013
Messages
43
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 :(
 

Forum statistics

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

Some videos you may like

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