Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
I have a table (SampleResult) in which we have 2 columns let's say "RequestDate" and "CompletedDate", on the date we get requests is in RequestDate and the date we have completed is in CompletedDate.
So for a scenario, we have 3 requests on a particular date and their completed dates are different, then how can we find the maximum and minimum time taken by the requests on that date?
Like that, we have to find for the whole table.
Request ID | RequestDate | CompletedDate | TimeDiff | MinTime | MaxTime |
123 | 4/23/2023 | 4/26/2023 | |||
124 | 5/24/2023 | 5/25/2023 | |||
125 | 5/24/2023 | 5/26/2023 | |||
126 | 5/24/2023 | 5/27/2023 |
Solved! Go to Solution.
Hi @tiwari_456
You can try the following steps:
1. Create a calculated column as follows.
TimeDiff = DATEDIFF('Table'[RequestDate], 'Table'[CompletedDate], DAY)
2. Create several measures as follow:
MinTime = CALCULATE(MIN([TimeDiff]), ALLEXCEPT('Table', 'Table'[RequestDate]))
MaxTime = CALCULATE(MAX('Table'[TimeDiff]), ALLEXCEPT('Table', 'Table'[RequestDate]))
Is this the result you expect?
Best Regards,
Community Support Team _Yuliax
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi @tiwari_456
You can try the following steps:
1. Create a calculated column as follows.
TimeDiff = DATEDIFF('Table'[RequestDate], 'Table'[CompletedDate], DAY)
2. Create several measures as follow:
MinTime = CALCULATE(MIN([TimeDiff]), ALLEXCEPT('Table', 'Table'[RequestDate]))
MaxTime = CALCULATE(MAX('Table'[TimeDiff]), ALLEXCEPT('Table', 'Table'[RequestDate]))
Is this the result you expect?
Best Regards,
Community Support Team _Yuliax
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.