Forum Discussion

amandabus21's avatar
amandabus21
Icon for Helper V rankHelper 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's avatar
BeaBF
Icon for Super User rankSuper 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's avatar
amandabus21
Icon for Helper V rankHelper 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's avatar
    BeaBF
    Icon for Super User rankSuper 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