Dax formula not working

[teffita]

I have created the below dax, to try to get in how many days an order is late.

So the loic goes like this:

• if the order is created before 8pm Monday to Saturday, then it should be fulfilled the same day.
• If an order has been fulfilled after 8pm then the cut-off will move to the next day, so is like the order was made the next day before 8pm. 
• For Saturday - Sunday orders after 8 the cut-off moves to Monday as Sunday is not a working day.
• Sunday cannot be counted as failed day, as is not working day.

So ideally what I need is how many days have passed since an order that have not been fulfilled on time, for the same day fulfillments works however for some orders created before 8pm and fulfilled the next day or any other day shows like 0 also. 

I can't figure it out where is the issue.

Any help will be highly appreciatted.

DaysLate_Express = 
VAR CreatedTime = MAX(Orders[AdjustedTime])
VAR FulfilledTime = MAX(Orders[Fulfilment Date Adjusted])

-- Get the date values from the datetime
VAR CreatedDateOnly = DATEVALUE(CreatedTime)
VAR FulfilledDateOnly = DATEVALUE(FulfilledTime)

-- Get the created day of the week (Monday = 1, Sunday = 7)
VAR CreatedDay = WEEKDAY(CreatedDateOnly, 2)
VAR FulfilledDay = WEEKDAY(FulfilledDateOnly, 2)

-- Check if the order was created before or after 8 PM
VAR IsAfter8PM = IF(HOUR(CreatedTime) >= 20, 1, 0)

-- Adjusted created date based on creation time
VAR AdjustedCreatedDate = 
    IF(
        IsAfter8PM = 1, 
        IF(CreatedDay = 6, CreatedDateOnly + 2,  -- Saturday after 8 PM moves to Monday
        IF(CreatedDay = 7, CreatedDateOnly + 1,  -- Sunday moves to Monday
        CreatedDateOnly + 1  -- Weekday after 8 PM moves to the next day
        )),
        CreatedDateOnly  -- No adjustment for orders created before 8 PM
    )

-- Check if the fulfilled date is the same day as the adjusted created date
VAR IsSameDay = CreatedDateOnly = FulfilledDateOnly

-- Calculate the number of days late
VAR DaysLate = 
    IF(
        NOT IsAfter8PM && NOT IsSameDay,
        DATEDIFF(AdjustedCreatedDate, FulfilledDateOnly, DAY) - 
        DIVIDE(COUNTROWS(
            FILTER(
                {1, 2, 3, 4, 5, 6, 7},  -- Days of the week (1 = Monday, ..., 7 = Sunday)
                [Value] = 7 && [Value] >= WEEKDAY(AdjustedCreatedDate, 2) && [Value] <= WEEKDAY(FulfilledDateOnly, 2)
            )
        ), 1),  -- Count the number of Sundays in the range
        0  -- Return 0 if the order was fulfilled on the same day or if SLA was met
    )

-- Return the days late if SLA not met, otherwise 0
RETURN
    IF(NOT IsSameDay && NOT IsBlank(DaysLate), DaysLate, 0)

•  

[teffita]

Thanks so much!! you saved my day!! 

[Anonymous · Accepted solution]

Hi, @teffita 

Regarding the issue you raised, my solution is as follows:

1.First I have created the following table and the column names and data are the data you have given:

2. Below are the measure I've created for your needs:

DaysLate_Express = 
VAR CreatedTime = MAX(Orders[AdjustedTime])
VAR FulfilledTime = MAX(Orders[Fulfilment Date Adjusted])

VAR CreatedDateOnly = DATEVALUE(CreatedTime)
VAR FulfilledDateOnly = DATEVALUE(FulfilledTime)

VAR CreatedDay = WEEKDAY(CreatedDateOnly, 2)  
VAR IsAfter8PM = IF(HOUR(CreatedTime) >= 20, 1, 0)

VAR AdjustedCreatedDate = 
    SWITCH(
        TRUE(),
        IsAfter8PM = 1 && CreatedDay = 6, CreatedDateOnly + 2,  
        IsAfter8PM = 1 && CreatedDay = 7, CreatedDateOnly + 1, 
        IsAfter8PM = 1, CreatedDateOnly + 1, 
        CreatedDateOnly 
    )

VAR DaysLate = 
    IF(
        FulfilledDateOnly > AdjustedCreatedDate,
        DATEDIFF(AdjustedCreatedDate, FulfilledDateOnly, DAY) - 
        COUNTROWS(
            FILTER(
                {1, 2, 3, 4, 5, 6, 7}, 
                [Value] = 7 && [Value] >= WEEKDAY(AdjustedCreatedDate, 2) && [Value] <= WEEKDAY(FulfilledDateOnly, 2)
            )
        ),
        0  
    )

RETURN
    IF(DaysLate > 0, DaysLate, 0)

3.Here's my final result, which I hope meets your requirements.

Please find the attached pbix relevant to the case.

 
Of course, if you have any new discoveries or questions, please feel free to get in touch with us.
 

Best Regards,

Leroy Lu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.