Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

Is this even possible - dynamically "carry over" previous month values

Hi Community, I'm not sure if this is even possible and I hope my explanation is not too confusing.

 

I have a business case where an analysis is being done on inventory that is sitting unused on shelves, and calculating associated value of those products that are just sitting there.

 

I have a calculated column that takes the sum of the stock value for each product, on the latest day of each month.

 

What I would like to do is somehow "carry over" the last end of month value for each product, in perpetuity, to represent a product (and its associated value) remaining static if no further deliveries of that product are stocked AND if no further purchases of that product are made.

 

Is this possible? I have tried to illustrate what I would like to accomplish below. TIA for any insight!

 

 

  • Anonymous's avatar
    Anonymous
    4 years ago

    Hi Anonymous ,

     

    I think you can create a calculated table to achieve your goal.

    My Sample:

    Date table:

    Date =
ADDCOLUMNS (
    CALENDAR ( DATE ( 2021, 10, 01 ), DATE ( 2022, 03, 31 ) ),
    "Year", YEAR ( [Date] ),
    "Month", MONTH ( [Date] ),
    "YearMonth",
        YEAR ( [Date] ) * 100
            + MONTH ( [Date] )
)

    Calculated table:

    Table 2 = 
VAR _GENERATE =
    GENERATE ( VALUES ( 'Table'[SKU] ), SUMMARIZE('Date',[Year],[Month],[YearMonth]) )
VAR _ADDCOLUMN =
    ADDCOLUMNS (
        _GENERATE,
        "MAXDATE", CALCULATE ( MAX ( 'Table'[Stock Date] ) ),
        "Value", CALCULATE ( SUM ( 'Table'[End of Month Value] ) )
    )
VAR _Fill =
    ADDCOLUMNS (
        _ADDCOLUMN,
        "FILL",
            VAR _MAXDATE_PREVIOUS =
                MAXX (
                    FILTER (
                        _ADDCOLUMN,
                        [YearMonth] < EARLIER ( [YearMonth] )
                            && [SKU] = EARLIER ( [SKU] )
                    ),
                    [MAXDATE]
                )
            RETURN
                IF (
                    ISBLANK([Value]),
                    SUMX ( FILTER ( _ADDCOLUMN, [MAXDATE] = _MAXDATE_PREVIOUS ), [Value] ),
                    [Value]
                )
    )
RETURN
    _Fill

    Result is as below.

     

    Best Regards,
    Rico Zhou

     

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

     

2 Replies

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

    Anonymous , You should create measure like

     

    Inventory / OnHand
    [Intial Inventory] + CALCULATE(SUM(Table[Ordered]),filter(date,date[date] <=maxx(date,date[date]))) - CALCULATE(SUM(Table[Sold]),filter(date,date[date] <=maxx(date,date[date])))

     

    or


    Inventory / OnHand
    CALCULATE(firstnonblankvalue('Date'[Month]),sum(Table[Intial Inventory]),all('Date')) + CALCULATE(SUM(Table[Ordered]),filter(date,date[date] <=maxx(date,date[date]))) - CALCULATE(SUM(Table[Sold]),filter(date,date[date] <=maxx(date,date[date])))

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    Hi Anonymous ,

     

    I think you can create a calculated table to achieve your goal.

    My Sample:

    Date table:

    Date =
ADDCOLUMNS (
    CALENDAR ( DATE ( 2021, 10, 01 ), DATE ( 2022, 03, 31 ) ),
    "Year", YEAR ( [Date] ),
    "Month", MONTH ( [Date] ),
    "YearMonth",
        YEAR ( [Date] ) * 100
            + MONTH ( [Date] )
)

    Calculated table:

    Table 2 = 
VAR _GENERATE =
    GENERATE ( VALUES ( 'Table'[SKU] ), SUMMARIZE('Date',[Year],[Month],[YearMonth]) )
VAR _ADDCOLUMN =
    ADDCOLUMNS (
        _GENERATE,
        "MAXDATE", CALCULATE ( MAX ( 'Table'[Stock Date] ) ),
        "Value", CALCULATE ( SUM ( 'Table'[End of Month Value] ) )
    )
VAR _Fill =
    ADDCOLUMNS (
        _ADDCOLUMN,
        "FILL",
            VAR _MAXDATE_PREVIOUS =
                MAXX (
                    FILTER (
                        _ADDCOLUMN,
                        [YearMonth] < EARLIER ( [YearMonth] )
                            && [SKU] = EARLIER ( [SKU] )
                    ),
                    [MAXDATE]
                )
            RETURN
                IF (
                    ISBLANK([Value]),
                    SUMX ( FILTER ( _ADDCOLUMN, [MAXDATE] = _MAXDATE_PREVIOUS ), [Value] ),
                    [Value]
                )
    )
RETURN
    _Fill

    Result is as below.

     

    Best Regards,
    Rico Zhou

     

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