topic Re: Is this even possible - dynamically "carry over" previous month values in DAX Commands and Tips

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

Anonymous — Wed, 01 Jun 2022 23:00:16 GMT

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!

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

amitchandak — Thu, 02 Jun 2022 02:38:34 GMT

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

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

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

Anonymous — Wed, 08 Jun 2022 08:07:28 GMT

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.