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.
Hello Power BI Community,
I'm a novice in Power BI and I'm currently struggling with linking data from a .CSV file with an excel file through a key column.
On the one hand, the .CSV file which I would name "Deliveries" contains a list of deliveries prepared during the day.
Basically, the "Deliveries' file looks like this :
Delivery number (String type) | Date of preparation (Date type in dd/MM/yyyy format) | Hour of preparation (Time type in hh:mm:ss format) |
00001 | 01/04/2021 | 08:01:59 |
00002 | 01/04/2021 | 08:35:42 |
00003 | 01/04/2021 | 08:52:06 |
00004 | 01/04/2021 | 09:03:18 |
00005 | 01/04/2021 | 09:19:26 |
00006 | 01/04/2021 | 09:28:32 |
00007 | 01/04/2021 | 09:47:41 |
00008 | 01/04/2021 | 10:15:36 |
00009 | 01/04/2021 | 10:28:14 |
00010 | 01/04/2021 | 10:51:22 |
So in the "Deliveries" file I've got 10 deliveries prepared on 01/04/2021 :
- 3 deliveries prepared in the 8h time slot
- 4 deliveries prepared in the 9h time slot
- 3 deliveries prepared in the 10h time slot
On the other hand, the excel file which I would name "Capacity" contains the production capacity of the team for the day and would be updated before the start of the day (file dropped in a declared folder).
The "Capacity" file looks like this :
Date (Date type in dd/MM/yyyy format) | Hour time slot (Time type in hh:mm:ss format) | Amount of preparators planned for the time slot (float type) |
01/04/2021 | 08:00:00 | 4,5 |
01/04/2021 | 09:00:00 | 6 |
01/04/2021 | 10:00:00 | 6 |
01/04/2021 | 11:00:00 | 3 |
01/04/2021 | 12:00:00 | 6 |
01/04/2021 | 13:00:00 | 6 |
01/04/2021 | 14:00:00 | 6 |
01/04/2021 | 15:00:00 | 4,5 |
01/04/2021 | 16:00:00 | 6 |
01/04/2021 | 17:00:00 | 6 |
01/04/2021 | 18:00:00 | 6 |
01/04/2021 | 19:00:00 | 3 |
I've created a measure named "hourly productivity target" based on the product of "Amount of preparators planned for the time slot" and a constant named "Target Productivity" = 10.
The DAX expression is :
Date (Date type in dd/MM/yyyy format) | Hour time slot (Time type in hh:mm:ss format) | Amount of preparators planned for the time slot (float type) | Hourly productivity target | Effective Hourly productivity | Delta |
01/04/2021 | 08:00:00 | 4,5 | 45 | 3 | -42 |
01/04/2021 | 09:00:00 | 6 | 60 | 4 | -56 |
01/04/2021 | 10:00:00 | 6 | 60 | 3 | -57 |
01/04/2021 | 11:00:00 | 3 | 30 | ||
01/04/2021 | 12:00:00 | 6 | 60 | ||
01/04/2021 | 13:00:00 | 6 | 60 | ||
01/04/2021 | 14:00:00 | 6 | 60 | ||
01/04/2021 | 15:00:00 | 4,5 | 45 | ||
01/04/2021 | 16:00:00 | 6 | 60 | ||
01/04/2021 | 17:00:00 | 6 | 60 | ||
01/04/2021 | 18:00:00 | 6 | 60 | ||
01/04/2021 | 19:00:00 | 3 | 30 |
Date of preparation (Date type in dd/MM/yyyy format) | Hour time slot (Time type in hh:mm:ss format) | Effective Hourly productivity |
01/04/2021 | 08:00:00 | 3 |
01/04/2021 | 09:00:00 | 4 |
01/04/2021 | 10:00:00 | 3 |
Solved! Go to Solution.
Hi @Anonymous ,
You need to create a new column in Deliveries:
new Hour of preparation = TIME(HOUR(Deliveries[Hour of preparation (Time type in hh:mm:ss format)]),0,0)
Then you can create relationship between capcity and deliveries by the new column
For your expected output, you can use the following measure:
hourly productivity target = MAX(Capacity[Amount of preparators planned for the time slot (float type)])*10
effective hourly productivity = CALCULATE(COUNT(Deliveries[Delivery number (String type)]),FILTER(Deliveries,Deliveries[Date of preparation (Date type in dd/MM/yyyy format)] = MAX(Capacity[Date]) && Deliveries[new Hour of preparation] = MAX(Capacity[Hour time slot])))
Delta = [effective hourly productivity]-[hourly productivity target]
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Best Regards,
Dedmon Dai
Thank you very much for all the details, Dedmon Dai.
It works perfectly ! 😁
Hi @Anonymous ,
You need to create a new column in Deliveries:
new Hour of preparation = TIME(HOUR(Deliveries[Hour of preparation (Time type in hh:mm:ss format)]),0,0)
Then you can create relationship between capcity and deliveries by the new column
For your expected output, you can use the following measure:
hourly productivity target = MAX(Capacity[Amount of preparators planned for the time slot (float type)])*10
effective hourly productivity = CALCULATE(COUNT(Deliveries[Delivery number (String type)]),FILTER(Deliveries,Deliveries[Date of preparation (Date type in dd/MM/yyyy format)] = MAX(Capacity[Date]) && Deliveries[new Hour of preparation] = MAX(Capacity[Hour time slot])))
Delta = [effective hourly productivity]-[hourly productivity target]
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Best Regards,
Dedmon Dai
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 |
---|---|
107 | |
100 | |
80 | |
63 | |
58 |
User | Count |
---|---|
148 | |
111 | |
94 | |
84 | |
67 |