Calculate sum of cells based on date/time combinations

[Anonymous]

Hello,

I am trying to create a measurement which is based on transportation order data. The data can be stated in the following way:

Order number	Order delivery date	Order creation date	Order creation time	Weight (kg)
3242	02-02-2020	30-01-2020	08:15	3
1241	02-02-2020	31-01-2020	16:30	2
1249	02-02-2020	01-02-2020	12:30	5
4255	02-02-2020	01-02-2020	17:00	1
3331	03-02-2020	01-02-2020	16:00	3
3654	03-02-2020	02-02-2020	13:00	4
7231	03-02-2020	02-02-2020	16:30	2
...	...	...	...	...

In my case, 90% of the orders arrive the day before the delivery date. For each order delivery date, I would like to calculate the weight level of all orders combined for the day preceding the delivery date at specific moments in time. I would like to create the following table:

Order delivery date	Weight level preceding day order delivery date (before 12:00)	Weight level preceding day order delivery date (before 16:00)	Weight level preceding day order delivery date (before 20:00)
02-02-2020	5	10	11
03-02-2020	3	7	9
...	...	...	...

Could someone help me with this case? I have tried to sum the weight by comparing the date/time combinations of orders, but I did not manage to solve it. Thank you.

[FrankAT]

Hi,

take a look a the following figure:

Regards FrankAT

[Anonymous · Accepted solution]

Hi

It would be far easier if you could add a column to your order that cohort your data by hours.
So, in this example, add a column
OrderCohort=
IF(
   Order[Order creation time]<=12;12;
  IF (Order[Order creation time]<=16; 16;
  IF (Order[Order creation time]<=20; 20;24)))

(please note, I write pseudocode on the fly, so fix the syntax as needed)
(also you can achieve and it would be much easier with a SWITCH(TRUE()) construct)

You can now create a matrix with DATE in the ROW and OrderCohort in the columns

Then your measure in the value will be Weight=SUMX(Order;Order[Weight])

[Anonymous]

@Anonymous, I have managed to solve my problem with your help. Thank you.

[Anonymous]

Dear @Anonymous,

Thank you for your message. I have tried to implement your suggestion. It partly works. I would like to calculate the weight level for the day preceding the delivery day. Your suggestion calculates the weights up to the desired time stamp. However, when one order is created 3 days before the delivery date at 17:30, it is recorded as being between 16:00 and 20:00. However, the order should be recorded as being before 12:00 the day preceding the delivery day, since the order was created 3 days before. I hope you could help me.

Also, since I only have weekdays (and no subsequent dates because of the weekends), this might impede finding a working solution.

[Anonymous]

So this solution takes all the orders DELIVERED on a day up to that time and sum the weight.
You want to calculate all the orders DELIVERED ON THE PREVIOUS DAY (and only previous day) up to that time.

Do you have a date dimension? If not, please create it (look up on the web "how to create a date dimension in powerbi") 

[Anonymous]

Dear @Anonymous,

90% of the orders at a transportation company arrive the day preceding the delivery day. So, most customers order today and would like to receive their order tomorrow. I would like to track the weight level at specific moments in time on the day preceding the delivery day, more specifically at 12:00, 16:00, and 20:00. So, I can track how the weight level that is to be delivered tomorrow evolves over time. 10% of the orders may have been created two days preceding the delivery day, and should be taken into account being before 12:00 at the day preceding the delivery day. I hope that I clarified my case.

I do not have a date dimension yet, which I can do.