DAX Help - Measure based on different time slice

[Sadhikari]

Hi All,

Need some help with a calculation based on dataset below.

Dataset tables

1.   Orders    
2.   Order Date
3.   Order Hour
4.   Ship Date
5.   Ship Hour

Model Relationship

1. Orders[OrderDate]   -> Order Date[OrderDate]
2. Orders[OrderHour]  -> Order Hour[OrderHour]
3. Orders[ShipmentDate -> Ship Date [ShipDate]
4. Orders[Shipment Hour]  -> Ship Hour [ShipHour]

Sample Orders table data

Order	OrderDate	Order Date Time	Order Hour	ShipmentDate	Shipment Date Time	Shipment Hour
1	9/1/2024	9/1/24 4:00 AM	4	9/1/2024	9/1/24 4:30 AM	4
2	9/1/2024	9/1/24 4:00 AM	4	9/1/2024	9/1/24 5:00 AM	5
3	9/1/2024	9/1/24 5:00 AM	5	9/1/2024	9/1/24 5:00 AM	5
4	9/1/2024	9/1/24 5:00 AM	5	9/1/2024	9/1/24 5:30 AM	5
5	9/1/2024	9/1/24 5:00 AM	5	9/1/2024	9/1/24 5:40 AM	5
6	9/1/2024	9/1/24 8:00 AM	8	9/1/2024	9/1/24 8:30 AM	8
7	9/1/2024	9/1/24 9:00 AM	9	9/1/2024	9/1/24 10:30 AM	10
8	9/1/2024	9/1/24 10:00 AM	10	9/1/2024	9/1/24 10:50 AM	10

Desired output : 

Wanted visual  with main data to be filter based on order date as a slicer 

but visual should show break down by shipment. 

Generally if i show data by shipment hours then   for  ship hour 4 it will have order placed  as 1 ,  for ship hour 5 it will have order placed as 4  but i want to display order placed based on  order hour for shipment hour slice.

Desired output

Shipment hour	Order Shipped	Order Placed
4	1	2
5	4	3
6	0	0
7	0	0
8	1	1
9	0	1
10	2	1
Total	8	8

Can you please advice  what approach should i take for this ?

thanks

[Anonymous]

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.

[Sadhikari]

Hi ,

thanks for the advise but somehow allexcept not giving desired results also this calculation not showing total value.

[Anonymous · Accepted solution]

Hi @Sadhikari ,

Please try using the following DAX formula to create measures.

Orders Shipped = COUNTROWS('Orders') + 0

Orders Placed = 
VAR _currentSHIPHOUR = MAX('Ship Hour'[Ship Hour])
VAR _vtable = SUMMARIZE(ALLSELECTED('Orders'),'Orders'[Order Hour],"_Count",COUNT(Orders[Order Hour]))
RETURN
IF(HASONEVALUE('Ship Hour'[Ship Hour]),SUMX(FILTER(_vtable,[Order Hour]=_currentSHIPHOUR),[_Count]),SUMX(_vtable,[_Count])) + 0

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.

[Selva-Salimi]

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. 

[Sadhikari]

The model i shared is a subset of main model  so for some reason we need the date and hours dimension but took the approach of adding new  disconnected  'Hour' table and used this table to show visual trends.

Thanks for the help and advise

calculation that worked for me 

Orders By Order Hours =
CALCULATE(
COUNTROWS('Orders'),
TREATAS(VALUES(Hours[Hour]),'Order Hours'[OrderHour])
)

Orders By Order Hours =
CALCULATE(
COUNTROWS('Orders'),
TREATAS(VALUES(Hours[Hour]),'Ship Hours'[ShipHour])
)