Cumulative quantity

[K-LED]

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

[nandukrishnavs]

@K-LED 

Can you provide a sample table and expected output values.

[K-LED]

@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!

[Anonymous]

Here's how to properly deal with weeks.

https://www.sqlbi.com/articles/week-based-time-intelligence-in-dax/

Best
D

[K-LED]

many thanks @Anonymous