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

[Anonymous]

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

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.

[amitchandak]

@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])))