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   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.

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.