SHIPMENT LEADTIME EXCLUDING SUNDAYS AND HOLIDAYS

[hmenco]

Hi!

I have been working on this equation for a while and it seems that I can't get the right answer CONSISTENTLY.

So I have here the Creation date (sales order placed) and Shipment end (sales order delivered). I have to get the difference between the two excluding the sundays and holidays.

On my calendar table, I have tagged 1 as working day, 0 for non-working (sundays and holidays)

I need to get the number of days between the Creation Date and Shipment End excluding the Sundays and Holidays.

I am using this DAX and it doesn't seem to work consistently.

Query1 = IF(ISBLANK(fcons[Shipment End]),BLANK(), CALCULATE(COUNTROWS('calendar'), DATESBETWEEN('calendar'[Date],fcons[Creation Date],fcons[Shipment End]-1), 'calendar'[IsNotSunday&NatHoliday]=1))

Hoping someone would help. Thanks!

[PC2790 · Accepted solution]

Hi @hmenco ,

Try to create a new DAX column with the following DAX code:

SubDtcrtnDtDiff =
VAR CalendarDays =
CALCULATE (
SUM ( 'calendar'[IsNotSunday&NatHoliday] ),
DATESBETWEEN (
'calendar'[Date],
'fcons'[Creation Date],
fcons[Shipment End] - 1
),
'calendar'[IsNotSunday&NatHoliday] = 1
)
VAR Net =
IF (
OR ( ISBLANK ( 'fcons'[Creation Date] ), ISBLANK ( fcons[Shipment End] ) ),
BLANK (),
IF (
DATEDIFF ( fcons[Shipment End], 'fcons'[Creation Date], DAY ) = 0,
0,
CalendarDays
)
)
RETURN
Net

Please mark this as a solution if it is exactly you were looking for.

[hmenco · Accepted solution]

This worked as well, my DAX worked when I toggled it to Don't Summarize. Thanks!

[PC2790 · Accepted solution]

Hi @hmenco ,

Try to create a new DAX column with the following DAX code:

SubDtcrtnDtDiff =
VAR CalendarDays =
CALCULATE (
SUM ( 'calendar'[IsNotSunday&NatHoliday] ),
DATESBETWEEN (
'calendar'[Date],
'fcons'[Creation Date],
fcons[Shipment End] - 1
),
'calendar'[IsNotSunday&NatHoliday] = 1
)
VAR Net =
IF (
OR ( ISBLANK ( 'fcons'[Creation Date] ), ISBLANK ( fcons[Shipment End] ) ),
BLANK (),
IF (
DATEDIFF ( fcons[Shipment End], 'fcons'[Creation Date], DAY ) = 0,
0,
CalendarDays
)
)
RETURN
Net

Please mark this as a solution if it is exactly you were looking for.