topic Re: Days Between Summed Up in DAX Commands and Tips

Days Between Summed Up

amandabus21 — Thu, 13 Jul 2023 18:01:55 GMT

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.

Re: Days Between Summed Up

BeaBF — Thu, 13 Jul 2023 18:59:15 GMT

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

Re: Days Between Summed Up

amandabus21 — Thu, 13 Jul 2023 19:59:05 GMT

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?

Re: Days Between Summed Up

BeaBF — Fri, 14 Jul 2023 11:49:23 GMT

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

Re: Days Between Summed Up

amandabus21 — Fri, 14 Jul 2023 17:59:31 GMT

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

Re: Days Between Summed Up

BeaBF — Fri, 14 Jul 2023 18:04:14 GMT

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

Re: Days Between Summed Up

amandabus21 — Fri, 14 Jul 2023 18:48:45 GMT

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.