Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago

Cumulative quantity

Hello,

 

I have to calculate the cumulative quantity by weeks number. I have created a custom column "Wee Nr" that return the week number from a column data.

 

I have tried with this formulas without success :

Cumulative quantity by Week = 
CALCULATE(
   SUM('Table'[Quantity]), 
   FILTER(
      ALL('Table'[Week Nr]), 
     'Table'[Week Nr] <= Max('Table'[Week Nr])
   )
)

 

Cumulative quantity by weeks = 
CALCULATE(
    SUM('Table'[Quantity]),
    FILTER(
        ALLSELECTED(Table.[Date]),
        WEEKNUM(Table.[Date], 2) <= MAX([Week Nr]) && 'Table'.[Date] <= MAX('Table'.[Date])
    )
)

 

Does anyone have an idea what's wrong with my formulas ?

 

Many thanks

4 Replies

  • nandukrishnavs's avatar
    nandukrishnavs
    Icon for Community Champion rankCommunity Champion
    6 years ago

    Anonymous 

     

    Can you provide a sample table and expected output values.

     

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      nandukrishnavs 

       

      I've created this quick measure which return exactly what I need 

      CALCULATE(
    SUM('Table'[Quantity]),
    FILTER(
        ALLSELECTED('Table'[Week Nr]),
        ISONORAFTER('Table'[Week Nr], MIN('Table'[Week Nr]), DESC)
    )
)

       

      And the result is :

       

      Actually the problem is when I start to use the filter "Week Nr" it doesn't work well and it displays me the total per week and not the cumulative.

       

      Note: the "Week Nr" column is a whole number as type. How can I fix that ?

       

      Thank you for your reply!