Roll over the data for rest of the months

[yuvara]

I have an interesting issue to deal with. I am working on the On Time Delivery Report, and the requirement is, if the order is Delivered then the OTD is difference between closed date and promised Ship date. But if the order is Not yet delivered and Promised Ship Date is greater than today, then the salesorder should be tagged as late for the current and rest of the months, till the order is closed/Delivered.

For Example :

Order Number	Promised Delivery Date	Actual Delivery Date
Order 1	2-Oct-22	2-Nov-22
Order 2	2-Sep-22	1-Sep-22
Order 3	2-Sep-22

And the desired result is as follows:

Order	Aug	Sep	Oct	Nov	Dec
Order 1				LATE
Order 2		ON TIME
Order 3		LATE	LATE	LATE	LATE

I think the formulae for Order 1 and 2 is simple, but for order 3 - I am not sure how to accomplish it. Any help would be really appreciated

Thanks

Yuvaraj

[yuvara · Accepted solution]

Hi CST, 

Thanks for the solution, it almost statisfies 90% of the scenarios. Just one sceniro it missed out is the past date

the result looks like :

Order 4 - should be Late from March to September as well. Working on tweaking your DAX, but its really helpful.

[v-easonf-msft]

Hi, @yuvara 

If you have a saperate calendar table, you can try measure formula like:

Tag = 
IF (
    MAX ( 'Table'[Actual Delivery Date] ) <> BLANK ()
        && MAX ( 'Table'[Promised Delivery Date] ) > MAX ( 'Table'[Actual Delivery Date] ),
    "Ontime",
    "Late"
)

Result = 
VAR _month =
    MAX (
        MAX ( 'Table'[Actual Delivery Date] ),
        MAX ( 'Table'[Promised Delivery Date] )
    )
RETURN
    IF (
        MAX ( 'Table'[Actual Delivery Date] ) <> BLANK (),
        IF ( MONTH ( MAX ( 'Calendar'[Date] ) ) = MONTH ( _month ), [Tag] ),
        IF ( MONTH ( MAX ( 'Calendar'[Date] ) ) >= MONTH ( _month ), [Tag] )
    )

Best Regards,
Community Support Team _ Eason
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[yuvara · Accepted solution]

Hi CST, 

Thanks for the solution, it almost statisfies 90% of the scenarios. Just one sceniro it missed out is the past date

the result looks like :

Order 4 - should be Late from March to September as well. Working on tweaking your DAX, but its really helpful.

[yuvara · Accepted solution]

So, I tweaked your DAX as below:

Result =

VAR _month =

    MAX (

        MAX ( 'Table'[Actual Delivery Date] ),

        MAX ( 'Table'[Promised Delivery Date] )

    )

RETURN

   IF (

        MAX ( 'Table'[Actual Delivery Date] ) <> BLANK (),

        IF ( MONTH ( MAX ( 'Calendar'[Date] ) ) >=   month(MAX ( 'Table'[Promised Delivery Date] )) && month(MAX ( 'Calendar'[Date] ))  <=  month(MAX ( 'Table'[Actual Delivery Date] )), [Tag]),

        IF ( MONTH ( MAX ( 'Calendar'[Date] ) ) >= MONTH ( _month ), [Tag] )

    )

[FreemanZ]

don't understand this part "the requirement is, if the order is Delivered then the OTD is difference between closed date and promised Ship date. "

[yuvara]

Sorry -  "the requirement is, if the order is Delivered then the OTD is difference between Actual Delivery date and promised Ship date. "