Forum Discussion

mr_oli's avatar
mr_oli
Helper I
6 years ago
Solved

Adding value from previous week to next week

Hello! I am looking for a way to add value from previous week to next week and so on with same Product ID and same Location ID This is what I got: Product Location Week Value 1a 123 ...
Need Help
Show and Tell
Tutorial Requests
  • nandic's avatar
    nandic
    6 years ago

    Hi mr_oli ,

     

    Could you try this formula:

    Cumulative Value =
    CALCULATE(
         SUM(Sheet1[Value]),
         ALLEXCEPT(Sheet1,Sheet1[Product],Sheet1[Location]),
         Sheet1[Week]<=EARLIER(Sheet1[Week])
    )
     
     
    Cheers,
    Nemanja
     
  • v-alq-msft's avatar
    v-alq-msft
    6 years ago

    Hi, mr_oli 

     

    Based on your description, I created data to reproduce your scenario. The pbix file is attached in the end.

     

     

    You may create a measure as below.

    Result = 
CALCULATE(
        SUM('Table'[Value]),
        FILTER(
            ALL('Table'),
            'Table'[Product]=SELECTEDVALUE('Table'[Product])&&
            'Table'[Location]=SELECTEDVALUE('Table'[Location])&&
            'Table'[Week]<=SELECTEDVALUE('Table'[Week])
        )
)

     

    Result:

     

    Best Regards

    Allan

     

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

     

mr_oli's avatar
mr_oli
Helper I
6 years ago

Thank you nandic. Its working great.

 

But could you please explain me how exactly ALLEXCEPT is working?
v-alq-msft used ALL instead of ALLEXCEPT which sound like completely opposite and but formula is giving the same result.

 

How is that possible?

nandic's avatar
nandic
Resident Rockstar
6 years ago

mr_oli , v-alq-msft  created measure using All function - this function removes any active filters and then adds specified filters in expression. For example, if you would like that some filters do not impact on measure (ie: selected week), you add expression filter(table,all(table[week])).

On the other hand, i created calculated column using AllExcept function - this function is equal to group by.
If i specified 2 columns in allexcept function, it means, sum amount by these two columns, other columns are not important for this measure.
It is commonly used when needed some percentage.
Original Measure: sum(amount)
Monthly Measure: calculate(sum(amount),allexcept(date[month])
Percentage: divide ([original measure]/ [monthy measure]

Here is also a nice overview of All functions: https://www.sqlbi.com/articles/managing-all-functions-in-dax-all-allselected-allnoblankrow-allexcept/