Aggregation of data from MSSQL Query

[Anonymous]

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

[v-lid-msft]

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.

[v-lid-msft]

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 

[parry2k]

@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.