Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hi All,
This is my first post here. I have been looking for a solution to my problem for a while now.
But couldn't found it yet. So here's is my problem
I have a dataset with orders and tasks on the order, see below
Order | Tasknumber | Department | Creation date |
16928134 | mv01 | mv | 18-4-2019 |
16928134 | mv02 | mv | 18-4-2019 |
16928134 | mv03 | mv | 15-5-2019 |
16928134 | mv04 | mv | 15-5-2019 |
16928139 | mv01 | mv | 20-1-2020 |
16928139 | mv02 | mv | 20-1-2020 |
16928139 | mv03 | mv | 13-2-2020 |
16928139 | mv04 | mv | 19-2-2020 |
16928139 | mv05 | mv | 19-2-2020 |
16928139 | mv06 | mv | 13-3-2020 |
16928139 | mv07 | mv | 18-3-2020 |
16928139 | mv08 | mv | 18-3-2020 |
16928139 | mv09 | mv | 23-3-2020 |
16928142 | mv01 | mv | 6-5-2019 |
16928142 | mv02 | mv | 6-5-2019 |
16928142 | mv03 | mv | 8-11-2020 |
16928142 | mv04 | mv | 20-11-2020 |
16928142 | mv05 | mv | 20-11-2020 |
16928149 | mv01 | mv | 21-1-2020 |
16928149 | mv02 | mv | 21-1-2020 |
16928149 | mv03 | mv | 6-7-2020 |
16928149 | mv04 | mv | 6-7-2020 |
16928149 | mv05 | mv | 6-7-2020 |
16928154 | mv01 | mv | 22-5-2020 |
16928154 | mv02 | mv | 5-8-2020 |
16928154 | mv03 | mv | 25-9-2020 |
16928154 | mv04 | mv | 25-9-2020 |
16928154 | mv06 | mv | 23-11-2020 |
16928154 | mv07 | mv | 23-11-2020 |
16928156 | mv01 | mv | 2-7-2020 |
16928156 | mv02 | mv | 2-7-2020 |
16928156 | mv03 | mv | 2-7-2020 |
16928158 | mv01 | mv | 3-12-2020 |
16928158 | mv02 | mv | 3-12-2020 |
16928158 | mv03 | mv | 16-1-2021 |
16928158 | mv04 | mv | 23-1-2021 |
16928158 | mv05 | mv | 23-1-2021 |
I want to have the difference per order from the first mv (department) task. ( I have even more departments, but if someone has the answer for this one already it would really help!)
See below example, for what I am looking for, column "Difference".
Order | Tasknumber | Department | Creation date | Difference |
16928134 | mv01 | mv | 18-4-2019 | 0 |
16928134 | mv02 | mv | 18-4-2019 | 0 |
16928134 | mv03 | mv | 15-5-2019 | 27 |
16928134 | mv04 | mv | 15-5-2019 | 27 |
16928139 | mv01 | mv | 20-1-2020 | 0 |
16928139 | mv02 | mv | 20-1-2020 | 0 |
16928139 | mv03 | mv | 13-2-2020 | 24 |
16928139 | mv04 | mv | 19-2-2020 | 30 |
16928139 | mv05 | mv | 19-2-2020 | 30 |
16928139 | mv06 | mv | 13-3-2020 | 53 |
16928139 | mv07 | mv | 18-3-2020 | 58 |
16928139 | mv08 | mv | 18-3-2020 | 58 |
16928139 | mv09 | mv | 23-3-2020 | 63 |
16928142 | mv01 | mv | 6-5-2019 | 0 |
16928142 | mv02 | mv | 6-5-2019 | 0 |
16928142 | mv03 | mv | 8-11-2020 | 552 |
16928142 | mv04 | mv | 20-11-2020 | 564 |
16928142 | mv05 | mv | 20-11-2020 | 564 |
16928149 | mv01 | mv | 21-1-2020 | 0 |
16928149 | mv02 | mv | 21-1-2020 | 0 |
16928149 | mv03 | mv | 6-7-2020 | 167 |
16928149 | mv04 | mv | 6-7-2020 | 167 |
16928149 | mv05 | mv | 6-7-2020 | 167 |
16928154 | mv01 | mv | 22-5-2020 | 0 |
16928154 | mv02 | mv | 5-8-2020 | 75 |
16928154 | mv03 | mv | 25-9-2020 | 126 |
16928154 | mv04 | mv | 25-9-2020 | 126 |
16928154 | mv06 | mv | 23-11-2020 | 185 |
16928154 | mv07 | mv | 23-11-2020 | 185 |
16928156 | mv01 | mv | 2-7-2020 | 0 |
16928156 | mv02 | mv | 2-7-2020 | 0 |
16928156 | mv03 | mv | 2-7-2020 | 0 |
16928158 | mv01 | mv | 3-12-2020 | 10 |
16928158 | mv02 | mv | 3-12-2020 | 154 |
16928158 | mv03 | mv | 16-1-2021 | 198 |
16928158 | mv04 | mv | 23-1-2021 | 205 |
16928158 | mv05 | mv | 23-1-2021 | 51 |
Hope you understand my question and hopefully someone can help me.
Regards,
Marc
Solved! Go to Solution.
Hi, @Anonymous
According to your description, you can create a measure to calculate the desired result.
Like this:
Measure =
VAR a =
MINX (
FILTER (
ALL ( 'Table' ),
[Department] = SELECTEDVALUE ( 'Table'[Department] )
&& [Order] = SELECTEDVALUE ( 'Table'[Order] )
),
[Creation date]
)
RETURN
( DATEDIFF ( a, SELECTEDVALUE ( 'Table'[Creation date] ), DAY ) )
If it doesn’t solve your problem, please feel free to ask me.
Best Regards
Janey Guo
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi, @Anonymous
According to your description, you can create a measure to calculate the desired result.
Like this:
Measure =
VAR a =
MINX (
FILTER (
ALL ( 'Table' ),
[Department] = SELECTEDVALUE ( 'Table'[Department] )
&& [Order] = SELECTEDVALUE ( 'Table'[Order] )
),
[Creation date]
)
RETURN
( DATEDIFF ( a, SELECTEDVALUE ( 'Table'[Creation date] ), DAY ) )
If it doesn’t solve your problem, please feel free to ask me.
Best Regards
Janey Guo
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi Janey,
Thank you so much. This was exactly what I was looking for!
I accepted it as a solution!
Have a nice day
@Anonymous Hi Mark, what is the formual to calculate the difference value. Can you please share it so that we can provide you the solution. Thanks
Proud to be a Super User!
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
116 | |
102 | |
78 | |
76 | |
49 |
User | Count |
---|---|
145 | |
108 | |
107 | |
89 | |
61 |