Forum Discussion
SHIPMENT LEADTIME EXCLUDING SUNDAYS AND HOLIDAYS
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.
Hoping someone would help. Thanks!
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
NetPlease mark this as a solution if it is exactly you were looking for.This worked as well, my DAX worked when I toggled it to Don't Summarize. Thanks!
2 Replies
PC2790Community Champion
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
NetPlease mark this as a solution if it is exactly you were looking for.This worked as well, my DAX worked when I toggled it to Don't Summarize. Thanks!