Perform Datediff from the same Column, but show result based on dynamically selected context

[RustyNails]

Hello - I have a peculiar situation. I have Data in this format

Product ID	Order Status	Forecast_Date
A000152024	Ordered	1/1/2022
A000152024	Submitted	1/12/2022
A000152024	Expected	3/1/2022
A000152024	Delivered	3/2/2022
B024765612	Ordered	1/12/2022
B024765612	Expected	4/2/2022
C004887763	Ordered	2/13/2022
C004887763	Submitted	2/22/2022
C004887763	Expected	5/2/2022
C004887763	Delivered	4/30/2022

(if I screw up the formatting here's the picture)

First, I want to be able to find the DateDiff for Forecast_Date for different Order statuses.

Next, I want to be able to select the Product ID in a slicer, and Order Status in a separate slicer, and then find the DateDiff for the two selected Order Status. For example, if I have my slicer selections as follows,

My output should look like this:

So essentially I need 3 columns: first selected date, second selected date, and then the datediff between the two. I understand that there may be data modeling manipulation needed...Also, if user selects more than one order status, it should always get the diff between the last two selections. Is there a way to achieve this? Keep in mind that this is sample. I have hundreds of Order Statuses I'm working with for any given Product ID.

Thanks!

[v-yanjiang-msft · Accepted solution]

Hi @RustyNails ,

According to your description, here's my solution.

Create five measures.

First Status =
MAXX (
    FILTER (
        'Table',
        'Table'[Forecast_Date]
            = MAXX (
                FILTER (
                    'Table',
                    'Table'[Forecast_Date] < MAXX ( 'Table', 'Table'[Forecast_Date] )
                ),
                'Table'[Forecast_Date]
            )
    ),
    'Table'[Order Status]
)

First selected date =
MAXX (
    FILTER (
        'Table',
        'Table'[Forecast_Date] < MAXX ( 'Table', 'Table'[Forecast_Date] )
    ),
    'Table'[Forecast_Date]
)

Second Status =
MAXX (
    FILTER (
        ALL ( 'Table' ),
        'Table'[Forecast_Date] = MAXX ( 'Table', 'Table'[Forecast_Date] )
    ),
    'Table'[Order Status]
)

Second selected date =
MAXX ( 'Table', 'Table'[Forecast_Date] )

Difference =
DATEDIFF ( [First selected date], [Second selected date], DAY )

Get the result:

I attach my sample below for your reference.

Best Regards,
Community Support Team _ kalyj

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

[RustyNails]

Hello! THANK YOU! this gets me to 90% of where I need to be!

The last 10% is how can I average the Difference measure? For example, if I select multiple Product ID's, can the Difference measure average the DateDiff between the two date selections?

[v-yanjiang-msft]

Hi @RustyNails ，

Yes, it can. If you select more than two values in the Order Status slicer, it only take the last two choice (based on the order:Ordered>Submited>Expected>Delivered) into consideration.

Best Regards,
Community Support Team _ kalyj

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