topic DAX for value from previous month different column in DAX Commands and Tips

DAX for value from previous month different column

AAndreeea — Fri, 21 Feb 2025 07:34:37 GMT

Hello,

I am looking for a DAX or an advice on:

- how to get the current Stock value in February (column D, the table has no date column) showing no balance for all the previuos months.

- how to get the current Stock value in March (2404,08) that is the net inventory of February (column H). The Net inventory is the sum of Stock Quantity + Quantity - KG Ordered) and replicate this month after month.

Thank you in advance for your help!

Re: DAX for value from previous month different column

quantumudit — Fri, 21 Feb 2025 07:41:29 GMT

Hello AAndreeea

Could you please provide sample data that fully represents your issue or question in a usable format, rather than a screenshot? Ensure the dataset is anonymized and does not contain any sensitive or unrelated information.

Additionally, it would be very helpful if you could share the expected outcome based on the provided data - this can be in any format, including a screenshot. Having a clear reference for comparison will significantly improve the chances of getting the correct solution in the first response.

For best practices, you may find the following links useful:

Thanks,

Udit

Re: DAX for value from previous month different column

freginier — Fri, 21 Feb 2025 14:09:04 GMT

Hi there!

If I understand correctly, you need a DAX formula that will carry Forward Previous Month’s Stock and Calculate Net Inventory in Power BI. Here's what you could use:

1. Use this DAX measure to get the previous month’s stock dynamically:

PreviousMonthStock =
VAR PrevMonth = MAX('Table'[Month_Num]) - 1
RETURN
CALCULATE(
SUM('Table'[Stock Quantity]),
FILTER('Table', 'Table'[Month_Num] = PrevMonth)
)

2. To ensure Net Inventory carries forward each month, use:

Net_Inventory =
VAR PrevInventory =
CALCULATE(
SUM('Table'[Net Inventory]),
FILTER('Table', 'Table'[Month_Num] = MAX('Table'[Month_Num]) - 1)
)
RETURN
IF(
ISBLANK(PrevInventory),
SUM('Table'[Stock Quantity]) + SUM('Table'[Quantity]) - SUM('Table'[KG Ordered]),
PrevInventory + SUM('Table'[Stock Quantity]) + SUM('Table'[Quantity]) - SUM('Table'[KG Ordered])
)

These two formulas should retrieves the stock quantity of the previous month and find data where the month is one less than the current. It will also check if there is any previous month's inventory, if no previous month exists, it calculates the current stock value normally. Otherwise, it adds the previous inventory and calculates the new stock level.

Hope this helps you!

😁😁

Re: DAX for value from previous month different column

Anonymous — Mon, 24 Feb 2025 05:28:52 GMT

Hi AAndreeea ,

Here I create a Calendar table to help calculation.

Calendar = SUMMARIZE ( ADDCOLUMNS ( CALENDAR ( DATE ( 2025, 01, 01 ), DATE ( 2025, 12, 31 ) ), "Year", YEAR ( [Date] ), "MONTHNAME", FORMAT ( [Date], "MMMM" ), "Order", YEAR ( [Date] ) * 100 + MONTH ( [Date] ), "KeyWord", COMBINEVALUES ( "-", YEAR ( [Date] ), FORMAT ( [Date], "MMMM" ) ) ), [Year], [MONTHNAME], [KeyWord], [Order] )

Add a Keyword column in your table.

KeyWord = COMBINEVALUES("-",[Calendar_Year],[Month_Name])

Relationship:

Measures:

Stock Quantity = IF ( MAX ( 'Calendar'[Order] ) = 202502, 5896.80, IF ( MAX ( 'Calendar'[Order] ) > 202502, CALCULATE ( SUM ( 'Table'[Quantity] ) - SUM ( 'Table'[KG Ordered] ), FILTER ( ALLSELECTED ( 'Calendar' ), 'Calendar'[Order] < MAX ( 'Calendar'[Order] ) ) ) + 5896.80 ) )

Net Inventory = VAR _RunningTotal = CALCULATE ( SUM ( 'Table'[Quantity] ) - SUM ( 'Table'[KG Ordered] ), FILTER ( ALLSELECTED ( 'Calendar' ), 'Calendar'[Order] <= MAX ( 'Calendar'[Order] ) ) ) + 5896.80 RETURN IF(HASONEVALUE('Calendar'[Order]), IF ( MAX ( 'Calendar'[Order] ) >= 202502, _RunningTotal, CALCULATE ( SUM ( 'Table'[Quantity] ) - SUM ( 'Table'[KG Ordered] ) ) ), _RunningTotal - CALCULATE ( SUM ( 'Table'[Quantity] ) - SUM ( 'Table'[KG Ordered] ),'Calendar'[Order] = 202501) )

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.