Forum Discussion

pmfk's avatar
pmfk
New Member
5 years ago
Solved

Running sum per category

Hi,

I have the following data

WeekCountryCityForecasted opening stockChanges in stock
2.2020USALA19413
3.2020USALA18027
4.2020USALA16629
5.2020USALA1109
2.2020USANYC1570
3.2020USANYC13217
4.2020USANYC13610
5.2020USANYC1550

 

Stock data for weeks, per cities in US.

I'd like to add "actual" opening stock per each week and city, based on the following logic:

1. For the first week the opening stock equals the forecasted one.

2. For next weeks opening stock = previous week opening stock + previous week changes in stock.

 

So, the expected output is the following:

WeekCountryCityForecasted opening stockChanges in stockOpening stock
2.2020USALA19413194
3.2020USALA18027207
4.2020USALA16629234
5.2020USALA1109263
2.2020USANYC1570157
3.2020USANYC13217157
4.2020USANYC13610174
5.2020USANYC1550184

Thank you in advance

 

  • v-alq-msft's avatar
    v-alq-msft
    5 years ago

    Hi, pmfk 

     

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

    Table:

     

    Calculated column:

     

    Year = VALUE(MID([Week],3,4))
WeekNum = VALUE(LEFT([Week],1))

     

     

    You may create a measure or a calculated column as below.

    Measure:

     

    Opening stock Measure = 
var minweek = 
CALCULATE(
    MIN('Table'[Week]),
    ALLEXCEPT('Table','Table'[Country],'Table'[City],'Table'[Year])
)
return
IF(
    MAX([Week])=minweek,
    MAX([Forecasted opening stock]),
    CALCULATE(
        SUM('Table'[Forecasted opening stock]),
        FILTER(
            ALLEXCEPT('Table','Table'[Country],'Table'[City],'Table'[Year]),
            [Week]=minweek
        )
    )+
    SUMX(
        FILTER(
            ALL('Table'),
            [Country]=MAX('Table'[Country])&&
            [City]=MAX('Table'[City])&&
            [Year]=MAX('Table'[Year])&&
            [Week]<MAX('Table'[Week])
        ),
        [Changes in stock]
    )
)

     

    Calculated column:

     

    Opening stock Column = 
var minweek = 
CALCULATE(
    MIN('Table'[Week]),
    ALLEXCEPT('Table','Table'[Country],'Table'[City],'Table'[Year])
    
)
return
IF(
    [Week]=minweek,
    [Forecasted opening stock],
    CALCULATE(
        SUM('Table'[Forecasted opening stock]),
        FILTER(
            ALLEXCEPT('Table','Table'[Country],'Table'[City],'Table'[Year]),
            [Week]=minweek
        )
    )+
    SUMX(
        FILTER(
            ALL('Table'),
            [Country]=EARLIER('Table'[Country])&&
            [City]=EARLIER('Table'[City])&&
            [Year]=EARLIER('Table'[Year])&&
            [Week]<EARLIER('Table'[Week])
        ),
        [Changes in stock]
    )
)

     

     

    Result:

     

    Best Regards

    Allan

     

    If this post helps,then consider Accepting it as the solution to help other members find it faster.

3 Replies

  • v-alq-msft's avatar
    v-alq-msft
    Icon for Community Support rankCommunity Support
    5 years ago

    Hi, pmfk 

     

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

    Table:

     

    Calculated column:

     

    Year = VALUE(MID([Week],3,4))
WeekNum = VALUE(LEFT([Week],1))

     

     

    You may create a measure or a calculated column as below.

    Measure:

     

    Opening stock Measure = 
var minweek = 
CALCULATE(
    MIN('Table'[Week]),
    ALLEXCEPT('Table','Table'[Country],'Table'[City],'Table'[Year])
)
return
IF(
    MAX([Week])=minweek,
    MAX([Forecasted opening stock]),
    CALCULATE(
        SUM('Table'[Forecasted opening stock]),
        FILTER(
            ALLEXCEPT('Table','Table'[Country],'Table'[City],'Table'[Year]),
            [Week]=minweek
        )
    )+
    SUMX(
        FILTER(
            ALL('Table'),
            [Country]=MAX('Table'[Country])&&
            [City]=MAX('Table'[City])&&
            [Year]=MAX('Table'[Year])&&
            [Week]<MAX('Table'[Week])
        ),
        [Changes in stock]
    )
)

     

    Calculated column:

     

    Opening stock Column = 
var minweek = 
CALCULATE(
    MIN('Table'[Week]),
    ALLEXCEPT('Table','Table'[Country],'Table'[City],'Table'[Year])
    
)
return
IF(
    [Week]=minweek,
    [Forecasted opening stock],
    CALCULATE(
        SUM('Table'[Forecasted opening stock]),
        FILTER(
            ALLEXCEPT('Table','Table'[Country],'Table'[City],'Table'[Year]),
            [Week]=minweek
        )
    )+
    SUMX(
        FILTER(
            ALL('Table'),
            [Country]=EARLIER('Table'[Country])&&
            [City]=EARLIER('Table'[City])&&
            [Year]=EARLIER('Table'[Year])&&
            [Week]<EARLIER('Table'[Week])
        ),
        [Changes in stock]
    )
)

     

     

    Result:

     

    Best Regards

    Allan

     

    If this post helps,then consider Accepting it as the solution to help other members find it faster.

  • amitchandak's avatar
    amitchandak
    Icon for Super User rankSuper User
    5 years ago

    pmfk , Create a week/date table and week there and join with this table.

    Add column to week table

    Week Rank = RANKX(all('Date'),'Date'[Week],,ASC,Dense)

     

    Create measures
    First Week Forecasted= CALCULATE(sum('Table'[Forecasted]), FILTER(ALLselected('Date'),'Date'[Week Rank]=min('Date'[Week Rank])))
    Till Last Week stock Change = CALCULATE(sum('Table'[stock Changes]), FILTER(ALL('Date'),'Date'[Week Rank]<=max('Date'[Week Rank])-1))

    if(isblank([Last Week stock Change]) ,[First Week Forecasted] , [First Week Forecasted]+[Till Last Week stock Change])

    • pmfk's avatar
      pmfk
      New Member
      5 years ago

      Thanks!

      Till Last Week stock Change = CALCULATE(sum('Table'[stock Changes]), FILTER(ALL('Date'),'Date'[Week Rank]<=max('Date'[Week Rank])-1))

      Why is it a measure and not a column?