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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

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
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

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

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.