Show min and max profit per hour

[hoangphuong753]

Hello, 

I have two table like this:

Cost: 

Price:

The cost value is per hour for every hour of a day. So for example the profit for 1/12/2023 at 1am would be 18-2 = 16.

I want to creat a matrix that show the min and max profit per hour. So I create a measure that calculate the profit per hour as AVERAGE('Price'[Price]) - AVERAGE('Cost'[Cost]) then use MINX and MAXX to iterate through the Price table. I also have a date and a time table that have relationship to the Cost and the Price table. However it doesn't work because Power BI would automatically use cost = 0 for the rest of the hour of the day. How can I fix it? Adding more hour rows for the cost table is not an option because the data set is huge with milion rows. 

Thank you very much

[hoangphuong753]

Thank you for your reply. I have simplified the data set in the question. The cost table actualy have 2-3 different types of cost, and I have to calculate the profit for each type each hour. Therefore if I do the merge with the Price table to show the cost there, it will double the rows in the price table. I don't really want to do that since the price table already have over 2 million rows. I really hope there is a way to do it with measures.

[AlexanderPrime]

In Power Query, Try duplicating the Date/Time column in the Price table, then set the formatting to Date Only.
Then format the Date Column in the Cost Table as Date Only, you can then either merge the two tables using the "new" Date column in the Price Table and the Date column in the cost table as the index for both. Then expand the merged table to show the cost in the Price table.  If you can do the merge then you'd end up with a column with the cost in the Price table and you can do your sum for the Profit amount, then can set up a matrix visual with the results.