Forum Discussion

Sadhikari's avatar
Sadhikari
Frequent Visitor
1 year ago
Solved

DAX Help - Measure based on different time slice

Hi All, Need some help with a calculation based on dataset below. Dataset tables   Orders       Order Date   Order Hour   Ship Date   Ship Hour   Model Relationship Orders[OrderDate]   -...
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.

Anonymous's avatar
Anonymous
Not applicable
1 year ago

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's avatar
Sadhikari
Frequent Visitor
1 year ago

Hi ,

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

  • Anonymous's avatar
    Anonymous
    Not applicable
    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.