Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
RustyNails
Helper III
Helper III

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

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

Product IDOrder StatusForecast_Date
A000152024Ordered1/1/2022
A000152024Submitted1/12/2022
A000152024Expected3/1/2022
A000152024Delivered3/2/2022
B024765612Ordered1/12/2022
B024765612Expected4/2/2022
C004887763Ordered2/13/2022
C004887763Submitted2/22/2022
C004887763Expected5/2/2022
C004887763Delivered4/30/2022

 

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

RustyNails_0-1666132421452.png

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,

RustyNails_2-1666132071700.png

My output should look like this:

 

RustyNails_1-1666132028441.png

 

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!

 

1 ACCEPTED SOLUTION
v-yanjiang-msft
Community Support
Community Support

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:

vkalyjmsft_0-1666175015868.png

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.

View solution in original post

3 REPLIES 3
v-yanjiang-msft
Community Support
Community Support

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:

vkalyjmsft_0-1666175015868.png

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.

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?

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.

vkalyjmsft_0-1666229957047.png

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.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.