DAX Help - Measure based on different time slice

Hi Sadhikari 

do you really need that much table to calculate your expectations?! I didn't think so.

Although there might be some solutions (like using userelationship) but the easiest one that I recommend is to use your order table, Date dimension and a table include 1 to 24 to cover 24 hours. and don't define any relationship between these tables. and just use this measuer:

measure shiped_orders := 

var selected_date := selectedvalue ( Date [date])

var selected_hour := selectedvalue ( Hour [hour])

return

calculate (countrows(order) , filter (orders, orders[shipmentdate]= selected_date && orders [shipment hour]=selected_hour))

and for the placed orders ....

measure placed_orders := 

var selected_date := selectedvalue ( Date [date])

var selected_hour := selectedvalue ( Hour [hour])

return

calculate (countrows(order) , filter (orders, orders[order date]= selected_date && orders [order hour]=selected_hour))

If this post helps, then I would appreciate a thumbs up 👍 and mark it as the solution to help the other members find it more quickly. 

Hi Sadhikari ,

Based on my testing, please try the following methods:

1.Create the new measure to calculate the Order shipped.

Orders Shipped = 
CALCULATE(
    COUNTROWS(Orders),
    Orders[Shipment Hour] = SELECTEDVALUE('Ship Hour'[Ship Hour])
) + 0

2.Create the new measure to calculate the order placed.

Orders Placed = 
CALCULATE(
    COUNTROWS(Orders),
    ALLEXCEPT(Orders, Orders[Shipment Hour]),
    Orders[Order Hour] = SELECTEDVALUE('Ship Hour'[Ship Hour])
) + 0

3.Drag the ship hour and two measures to table visual. The result is shown below.

Best Regards,

Wisdom Wu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.