Adding value from previous week to next week

[mr_oli]

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	30	1
1a	123	31	1
1a	123	32	1
1a	123	33	1
1a	123	34	1

And this is what I want to have:

Product	Location	Week	Value
1a	123	30	1
1a	123	31	2
1a	123	32	3
1a	123	33	4
1a	123	34	5

[nandic · Accepted solution]

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 · Accepted solution]

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.

[nandic · Accepted solution]

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

[kulkarnipriya87]

https://community.powerbi.com/t5/Desktop/Carry-forward-Value-from-previous-week-to-the-next-week/m-p...

[kulkarnipriya87]

https://community.powerbi.com/t5/Desktop/Carry-forward-Value-from-previous-week-to-the-next-week/m-p...

[mr_oli]

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]

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