Days Between Summed Up

[amandabus21]

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 Date Level 01]),

        TODAY(),

        DAY

    ),

    ALLEXCEPT(

        'Purchase Orders',

        'Purchase Orders'[Purchase Order #.Purchase Order # Level 01]

    )

)

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.

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. 

The bottom is an example of each purchase order with the delivery date and days between compared to the current Date.

[amandabus21]

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

[BeaBF]

@amandabus21 
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]))
RETURN
IF(CurrentDeliveryDate = MaxDeliveryDate, DATEDIFF(MaxDeliveryDate, TODAY(), DAY), BLANK())

BBF

[amandabus21]

Still not working. 

For example for PO 4500055313 its saying the Sum of Days Between is -108 from 7/26/2023. When it should be -12 Days.

[BeaBF]

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]

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]

@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