Trying to optimize a measure

[benZert]

Hi,

I've been tasked with 'speeding up' this DAX measure but I'm not sure where to start, does anybody have any suggestions on a way to make this measure work faster obviously still get the same result? Apologies if in the wrong forum

AvgStockOfDays2 = 
var avgcnt = max('Product'[CountOfDaysFwd])
     VAR FirstDayMAT = MAX ( PlanDueDate[PlanDueDate_DateSeqNumber])+1
     
      VAR LastDayMAT = FirstDayMAT + (avgcnt -1)
      
      Var StockOH = 
        CALCULATE (
    SUM ( Inventory_Measures[m_QtyOnHand] ),
    LASTNONBLANK ( LoadDate[LoadDatePK], CALCULATE ( SUM (Inventory_Measures[m_QtyOnHand] ) ) )
)

    VAR Period4W =
        CALCULATETABLE (
            VALUES (PlanDueDate[PlanDueDatePK] ),  ALLEXCEPT (
                'PlanDueDate',
               
                'PlanDueDate'[PlanDueDate_DayOfWeek]
            ),

            'PlanDueDate'[PlanDueDate_DateSeqNumber] >= FirstDayMAT 
                && PlanDueDate[PlanDueDate_DateSeqNumber] <= LastDayMAT
            

              )
              

		   VAR AvgMrp =
             CALCULATE (
                AVERAGEX ( Period4W,IF(ISBLANK( [QtyMrpFull]),0,[QtyMrpFull])
                 ),
                    ALL(PlanDueDate)
            )
          
            Var result =    
           DIVIDE(StockOH,AvgMrp,0)
        
    RETURN
        Result

Thanks 

[v-jingzhang]

Hi @benZert 

Is the [QtyMrpFull] a measure? If so, what is its formula? In this section, it will be evaluated twice. So if you can evaluate it only once, it may improve the performance to some degree. 

Try

AVERAGEX (
    Period4W,
    VAR _qtyMrpFull = [QtyMrpFull]
    RETURN
        IF ( ISBLANK ( _qtyMrpFull ), 0, _qtyMrpFull )
)

Best Regards,
Community Support Team _ Jing
If this post helps, please Accept it as Solution to help other members find it.