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
Anonymous
Not applicable

Struggling to create a measure

Hi everyone,

 

I am struggling to create a performance measure. Let's say I have a product that has to be validated by 3 different business units, I want to compute the delta time between team 1 and team 2, then between team 2 and team 3 for each of my products. The idea is then to create a matrix with every products and the time spent by the teams to validate it in order to measure efficiency.
Here's a screenshot if it can make things a bit more clear. Screenshot 2022-07-29 134509.png

Does anyone have an idea how this can be done ?

Thanks in advance ! 

1 ACCEPTED SOLUTION

Hi @Anonymous ,

According to your description, here's my solution. Create a calculated column.

Column =
DATEDIFF (
    MAXX (
        FILTER (
            'Table',
            'Table'[Product] = EARLIER ( 'Table'[Product] )
                && 'Table'[Team]
                    = EARLIER ( 'Table'[Team] ) - 1
        ),
        'Table'[Input_datetime]
    ),
    'Table'[Input_datetime],
    DAY
)

Get the result.

vkalyjmsft_0-1659516608376.png

I attach my sample below for 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

7 REPLIES 7
v-yanjiang-msft
Community Support
Community Support

Hi @Anonymous ,

What does delta time mean, do you want to get the date difference between the previous team and current team, like this:

vkalyjmsft_0-1659497524882.png

For team3, it should be 1 (2022/7/6-2022/7/5). For team2, it should be 0 (2022/7/5-2022/7/5). Did I understand it correctly? If not, could you please show the expected result.

 

Best Regards,
Community Support Team _ kalyj

Anonymous
Not applicable

Hi, 

Yes, that's it !

Hi @Anonymous ,

According to your description, here's my solution. Create a calculated column.

Column =
DATEDIFF (
    MAXX (
        FILTER (
            'Table',
            'Table'[Product] = EARLIER ( 'Table'[Product] )
                && 'Table'[Team]
                    = EARLIER ( 'Table'[Team] ) - 1
        ),
        'Table'[Input_datetime]
    ),
    'Table'[Input_datetime],
    DAY
)

Get the result.

vkalyjmsft_0-1659516608376.png

I attach my sample below for 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.

 

Anonymous
Not applicable

Hi, 
I got the following error but I don't understand why since I'm comparing 2 date values "DAX comparison operations do not support comparing values of type Text with values of type Number. Consider using the VALUE or FORMAT function to convert one of the values." 

 

Regards,

Hi @Anonymous ,

Is the data type of the Team column text? If so, change it to number.

Best Regards,
Community Support Team _ kalyj

amitchandak
Super User
Super User

@Anonymous , try a measure like

datediff([Input_datetime] , minx(filter(Table, [product] =earlier([product]) && [Team] <> earlier([Team]) && [Input_datetime] > earlier([Input_datetime] )),[Input_datetime]), day)

Anonymous
Not applicable

Thank you for your help ! However, it seems there's a problem with the measure since it's giving me inconsistent figures and most of the column values are blank Screenshot 2022-08-01 094801.png

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

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

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

Top Solution Authors