Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
2 years ago

How to get an initial 6-weeks sum?

Alert = IF([Closing Inventory]<0,1,0)

This is a measure.

 



Now i want to get sum of first 6 weeks only.

For example,
For Material Code 1043, the sum should be 2(Initial 6 Weeks sum).
Weeks can be dynamic.

How to get this?

 

Need Help

4 Replies

    • Anonymous's avatar
      Anonymous
      Not applicable
      2 years ago

      Weeks can be dynamic means the starting week could be any week like 23, 24 or 44.

       

      • danextian's avatar
        danextian
        Icon for Super User rankSuper User
        2 years ago

        Hi Anonymous 

         

        You must use either ALLSELECTED or ALL together with CALCULATE modify the current filter context. Using just MIN is equivalent to the MIN of the current row and not either what are visible or are in the table. Try:

        VAR MinWeek = 
CALCULATE( MIN('HUL (3)'[WeekNo]), ALLSELECTED ( 'HUL (3)' ) )
//min week based on the weeks that are currently visible

VAR MinWeek = 
CALCULATE( MIN('HUL (3)'[WeekNo]), ALL ( 'HUL (3)' ) )
//min week based on the weeks that are in the table

         

        And then in your RETURN statement you may or may not wrap the table name after FILTER in ALL or ALLSELECTED depending on the output you want to achieive.  Sample below is when using ALL

         

    • Anonymous's avatar
      Anonymous
      Not applicable
      2 years ago
      First 6 Weeks Sum new =

      VAR MinWeek = MIN('HUL (3)'[WeekNo])  // Find the minimum week value in the dataset
      VAR StartingWeek = Max(1, MinWeek)  // Ensure the starting week is at least 1
      VAR EndingWeek = StartingWeek + 5  // Calculate the ending week (starting week + 5)

      RETURN
      CALCULATE(
          [Alert],
          FILTER(
              'HUL (3)',
              'HUL (3)'[WeekNo] >= StartingWeek && 'HUL (3)'[WeekNo] <= EndingWeek
          )
      )
      Used this measure but did not get the correct value.