Forum Discussion

Helper V

3 years ago

Days Between Summed Up

Hello, I have a calculated column where I calcualte the Days between the Max Delivery Date per Purchase Order. CALCULATE( DATEDIFF( MAX('Purchase Orders'[Delivery Date.Delivery...

BeaBF

Super User

3 years ago

Hi amandabus21 ,

try with sumx:

Max Days Between =
VAR MaxDeliveryDate = MAX('Purchase Orders'[Delivery Date.Delivery Date Level 01])
RETURN
SUMX (
VALUES('Purchase Orders'[Purchase Order #.Purchase Order # Level 01]),
CALCULATE (
DATEDIFF (
MaxDeliveryDate,
TODAY(),
DAY
)
)
)

By using Sumx 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
Helper V
3 years ago
This kind of works as a measure except is not totaling correctly. When i dump in excel the correct sum is -14,073.

Also is there a way for it to work as a calc column?
- BeaBF
  Super User
  3 years ago
  amandabus21 Try with:
  Max Days Between =
  VAR MaxDeliveryDate = MAX('Purchase Orders'[Delivery Date.Delivery Date Level 01])
  RETURN
  IF('Purchase Orders'[Delivery Date.Delivery Date Level 01] = MaxDeliveryDate,
  DATEDIFF(MaxDeliveryDate, TODAY(), DAY),
  BLANK()
  )
  
  BBF