Forum Discussion

Sadhikari's avatar
Sadhikari
Frequent Visitor
2 years ago
Solved

DAX Help - Measure based on different time slice

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

OrderOrderDateOrder Date TimeOrder HourShipmentDateShipment Date Time Shipment Hour
19/1/20249/1/24 4:00 AM49/1/20249/1/24 4:30 AM4
29/1/20249/1/24 4:00 AM49/1/20249/1/24 5:00 AM5
39/1/20249/1/24 5:00 AM59/1/20249/1/24 5:00 AM5
49/1/20249/1/24 5:00 AM59/1/20249/1/24 5:30 AM5
59/1/20249/1/24 5:00 AM59/1/20249/1/24 5:40 AM5
69/1/20249/1/24 8:00 AM89/1/20249/1/24 8:30 AM8
79/1/20249/1/24 9:00 AM99/1/20249/1/24 10:30 AM10
89/1/20249/1/24 10:00 AM109/1/20249/1/24 10:50 AM10

 

 

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 hourOrder ShippedOrder Placed
412
543
600
700
811
901
1021
Total88

 

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

 

thanks

DAX
powerbi
  • Anonymous's avatar
    Anonymous
    1 year ago

    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.