Days Between Summed Up

Question

Hello,&nbsp;I have a calculated column where I calcualte the Days between the Max Delivery Date per Purchase Order.&nbsp;CALCULATE(&nbsp; &nbsp; DATEDIFF(&nbsp; &nbsp; &nbsp; &nbsp; MAX('Purchase Orders'[Delivery Date.Delivery Date Level 01]),&nbsp; &nbsp; &nbsp; &nbsp; TODAY(),&nbsp; &nbsp; &nbsp; &nbsp; DAY&nbsp; &nbsp; ),&nbsp; &nbsp; ALLEXCEPT(&nbsp; &nbsp; &nbsp; &nbsp; 'Purchase Orders',&nbsp; &nbsp; &nbsp; &nbsp; 'Purchase Orders'[Purchase Order #.Purchase Order # Level 01]&nbsp; &nbsp; ))&nbsp;&nbsp;It works fine but when I click "Sum" for the visualization, it starts adding up all the Days Between instead of just summing up the max days between.&nbsp;&nbsp;basically in the screen shot, i want it to sum the top left correttly. When i click sumn, the top right is what happens and is incorrect.&nbsp;&nbsp;The bottom is an example of each purchase order with the delivery date and days between compared to the current Date.&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;

beabf · Answer

Hi&nbsp;amandabus21&nbsp;,
try with sumx:
Max Days Between = VAR MaxDeliveryDate = MAX('Purchase Orders'[Delivery Date.Delivery Date Level 01])RETURNSUMX (VALUES('Purchase Orders'[Purchase Order #.Purchase Order # Level 01]),CALCULATE (DATEDIFF (MaxDeliveryDate,TODAY(),DAY)))
By using Sumx&nbsp;you ensure that only the maximum value for each purchase order is summed up when you apply the sum aggregation in the visual
BBF

amandabus21 · Answer

This kind of works as a measure except is not totaling correctly. When i dump in excel the correct sum is -14,073.&nbsp;&nbsp;&nbsp;Also is there a way for it to work as a calc column?

beabf · Answer

amandabus21&nbsp; Try with:Max Days Between = VAR MaxDeliveryDate = MAX('Purchase Orders'[Delivery Date.Delivery Date Level 01])RETURNIF('Purchase Orders'[Delivery Date.Delivery Date Level 01] = MaxDeliveryDate,DATEDIFF(MaxDeliveryDate, TODAY(), DAY),BLANK())
BBF

amandabus21 · Answer

That one did not work. Additonally I need a column becuase I need to run IF statements around it.

beabf · Answer

amandabus21&nbsp;Max Days Between =VAR CurrentOrder = 'Purchase Orders'[Purchase Order #.Purchase Order # Level 01]VAR CurrentDeliveryDate = 'Purchase Orders'[Delivery Date.Delivery Date Level 01]VAR MaxDeliveryDate = CALCULATE(MAX('Purchase Orders'[Delivery Date.Delivery Date Level 01]), ALLEXCEPT('Purchase Orders', 'Purchase Orders'[Purchase Order #.Purchase Order # Level 01]))RETURNIF(CurrentDeliveryDate = MaxDeliveryDate, DATEDIFF(MaxDeliveryDate, TODAY(), DAY), BLANK())BBF

Forum Discussion

Days Between Summed Up

6 Replies

Related content

Running sum

Day to Day average

Improper Sum with slicer

Data Days | Data Days Your Way

Filtering a Sum