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

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

Reply
rmcclung
Frequent Visitor

Please help - Calculate difference in dates categorized by a job schedule number

Hey Everyone,

 

I'm struggling on this one. My data is pulling in from an Excel sheet (attached) and I need to perform a calculation that pulls the difference (in days) between column C [Date Started] and column D [Date Comleted], but ONLY if it's the first order completed within that same [Job Schedule (Stamped)] in column A. You can see there are multiples of the same address in Column B with different [Job Schedule (Stamped)], so I would need each [Job Schedule (Stamped)] calculated by the first order completed date, regardless of the same address. I need to be able to Average this data on a Power BI Card.

 

Then, I need any results over 30 days to not be included. I highlighted the columns in GREEN that show which should be calculated based on this criteria and the one column in RED is an example of what should be excluded. However, I can probably just add a filter on the Power BI Card to exclude if the value is over 30 days, if that helps make the calculation easier!

 

 

 

Job Schedule (Stamped)AddressDate StartedDate CompletedFormula result should be:
JS-00000001234 Test St1/1/20231/5/20234
JS-00000001234 Test St1/15/20231/25/2023 
JS-00000001234 Test St2/5/20232/25/2023 
JS-11111111234 Test St5/1/20235/3/20232
JS-11111111234 Test St5/6/20235/7/2023 
JS-11111111234 Test St5/10/20235/15/2023 
JS-11111125555 Jane Doe1/1/20231/5/20234
JS-11111125555 Jane Doe1/15/20231/25/2023 
JS-11111125555 Jane Doe2/5/20232/25/2023 
JS-11111135555 Jane Doe4/1/20235/15/202344 days, so do not include in avg
JS-11111135555 Jane Doe5/6/20235/7/2023 
JS-11111135555 Jane Doe5/10/20235/15/2023 

 

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

Hi @rmcclung 

You can refer to the following calculated column.

Result =
VAR a =
    CALCULATE (
        MIN ( 'Table'[Date Started] ),
        FILTER (
            'Table',
            'Table'[ob Schedule (Stamped)] = EARLIER ( 'Table'[ob Schedule (Stamped)] )
        )
    )
RETURN
    IF (
        'Table'[Date Started] = a,
        IF (
            DATEDIFF ( 'Table'[Date Started], 'Table'[Date Completed], DAY ) < 30,
            DATEDIFF ( 'Table'[Date Started], 'Table'[Date Completed], DAY ),
            BLANK ()
        )
    )

Output

vxinruzhumsft_0-1684719968972.png

 

Best Regards!

Yolo Zhu

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

2 REPLIES 2
v-xinruzhu-msft
Community Support
Community Support

Hi @rmcclung 

You can refer to the following calculated column.

Result =
VAR a =
    CALCULATE (
        MIN ( 'Table'[Date Started] ),
        FILTER (
            'Table',
            'Table'[ob Schedule (Stamped)] = EARLIER ( 'Table'[ob Schedule (Stamped)] )
        )
    )
RETURN
    IF (
        'Table'[Date Started] = a,
        IF (
            DATEDIFF ( 'Table'[Date Started], 'Table'[Date Completed], DAY ) < 30,
            DATEDIFF ( 'Table'[Date Started], 'Table'[Date Completed], DAY ),
            BLANK ()
        )
    )

Output

vxinruzhumsft_0-1684719968972.png

 

Best Regards!

Yolo Zhu

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

This worked perfectly. Thank you so much! I'll mark this as "Accept as Solution"

Helpful resources

Announcements
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

Check out the February 2024 Power BI update to learn about new features.

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.