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,
can anybody help me with a possibly simple question:
From a merchandise management system for rental articles, I have a database query that lists which article leaves the warehouse on which day and reaches it again.
This results in at least two rows per article for the respective event. In addition, the article can of course also be planned for several events in the given period, so that it appears several times in the query result. In addition to the columns 'quantity', 'itemname', 'event', 'date of loading' and 'date of return', there is also a column 'time requirement', which specifies the preparation and postprocessing time for an article.
Now I would like to determine in PowerBI how many time units are required for each loading and unloading day. So, for example, you want to analyze which articles leave the warehouse in which quantity on day x and calculate the total accordingly.
Example:
<itemname> | <quantity> | <event> | <date of loading> | <date of return> | <time requirement> | <task> --------------------------------------------------------------------------------------------------------------------- Surface Book| 5 | Kickoff 2020 | 2020-02-05 10:00:00 | 2020-02-09 08:00:00 | 15 | loading Surface Book| 5 | Kickoff 2020 | 2020-02-05 10:00:00 | 2020-02-09 08:00:00 | 15 | unloading Headphones | 15 | Kickoff 2020 | 2020-02-05 10:00:00 | 2020-02-09 08:00:00 | 15 | loading Headphones | 15 | Kickoff 2020 | 2020-02-05 10:00:00 | 2020-02-09 08:00:00 | 15 | unloading ... Surface Book| 2 | Conferency xy | 2020-02-05 16:00:00 | 2020-02-14 06:00:00 | 15 | loading Surface Book| 2 | Conferency xy | 2020-02-05 16:00:00 | 2020-02-14 06:00:00 | 15 | unloading ... Surface Book| 2 | Exhibition xy | 2020-03-01 11:00:00 | 2020-03-07 06:00:00 | 15 | loading Surface Book| 2 | Exhibition xy | 2020-03-01 11:00:00 | 2020-02-07 06:00:00 | 15 | unloading ...
The result should be something like:
On 5th of february we need (5 x 15) + (15 x 15) + (2 x 15) = 330 time units for loading...
I hope I was able to express myself reasonably well.
Regards
Lars
Hi @Anonymous ,
How about the result after you follow the suggestions mentioned in my original post?Could you please provide more details about it If it doesn't meet your requirement?
Best regards,
Community Support Team _ Dong Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi @Anonymous ,
we can use the following two measure to meet your requirement.
loadingTimes = SUMX ( FILTER ( 'Table', [task] = "loading" ), [quantity] * [time requerement] )
unloadingTimes = SUMX ( FILTER ( 'Table', [task] = "unloading" ), [quantity] * [time requerement] )
If it doesn't meet your requirement, kindly share your sample data and excepted result to me if you don't have any Confidential Information. Please upload your files to One Drive and share the link here.
BTW, pbix as attached.
Best regards,
Community Support Team _ DongLi
If this post helps, then please consider Accept it as the solution to help the other members find it more
@Anonymous add following measures
Quantity = SUM ( Table[Quantity] ) Time Required = MAX ( Table[Time Required] ) Loading Time = CALCULATE ( [Quantity] * [Time Required], Table[Task] = "Loading" ) Unloading Time = CALCULATE ( [Quantity] * [Time Required], Table[Task] = "Unloading" )
You can use loading and unloading time measure in your visuals.
Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!
Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo
If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤
Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.
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 |
---|---|
106 | |
94 | |
77 | |
65 | |
53 |
User | Count |
---|---|
147 | |
106 | |
104 | |
87 | |
61 |