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.

     

v-alq-msft's avatar
v-alq-msft
Community Support
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.