topic Roll over the data for rest of the months in DAX Commands and Tips

Roll over the data for rest of the months

yuvara — Wed, 23 Nov 2022 17:19:16 GMT

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

Re: Roll over the data for rest of the months

FreemanZ — Thu, 24 Nov 2022 02:25:15 GMT

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. "

Re: Roll over the data for rest of the months

v-easonf-msft — Thu, 24 Nov 2022 08:11:36 GMT

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.

Re: Roll over the data for rest of the months

yuvara — Thu, 24 Nov 2022 10:30:08 GMT

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.

Re: Roll over the data for rest of the months

yuvara — Thu, 24 Nov 2022 10:31:22 GMT

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

Re: Roll over the data for rest of the months

yuvara — Thu, 24 Nov 2022 10:38:19 GMT

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] )

)