Forum Discussion
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 | 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 |
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.
6 Replies
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?
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/
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.
