Use Column A if its Current Month to solve for X, And Use last month's column X for the next months

[Ryiahj]

Is this possible? I've spent the last 2 days searching and trying different stuff but end up in the same results.

I've tested a couple of approaches but it always filters to the current month instead of using the previous month when I'm putting it in a Matrix table.

Chart:

I have a Matrix Table which have Materials on Rows, Months on Columns and different Measures on Values.

Problematic Measure:

I am trying to get the monthly Est. Ending Inventory which has the formula of

If Current Month:

Stocks on Hand - (Forecast - Sales) + Incoming Shipments

For the Next Months

Previous Months Est Ending Inventory - Forecast + Shipment

Example

If I'm solving for June, I'll have

Est End Invty = June's SOH - (June's Forecast + June's Sales) + June's Incoming Ships

If I'm going to solve for July

June's Est End Invty - July's Forecast + July's incoming Ships

If for Aug

July's Est End Invty - Aug'sForecast + Aug's incoming Ships

and so on.....

What is currently happening is, if I put the measure in the matrix, I'm not getting the previous month's est end inventroy. My current month is showing the correct result but for the next months I cannot make it use the last months value for the solve Est. End Inventory.  

Here's my current formula

Est. End Invy =

    var adjustedDate = IF(MONTH(TODAY()) > 3, MONTH(TODAY()) -3, MONTH(TODAY()) +  9)
    VAR currentMonth = 
        CALCULATE(
            MAX( 0 , [Total Stocks on Hand] - ABS ([Total Sales Outlook] - [Total Sales] ) + [Total Incoming Shipment] ),
        FILTER (
            ALLEXCEPT(DIM_CALENDAR_SALES, DIM_CALENDAR_SALES[Fiscal Month]),
            DIM_CALENDAR_SALES[FSDATE] = MAX(DIM_CALENDAR_SALES[FSDATE])))
        
    VAR next = 
            MAX( 0, currentMonth - [Total Sales Outlook] + [Total Incoming Shipment] )

    VAR nextMonths =
            MAX( 0, next - [Total Sales Outlook] + [Total Incoming Shipment] )

        VAR result =
        IF(MONTH(MAX(DIM_CALENDAR_SALES[FSDATE])) = adjustedDate, currentMonth,
            IF(DATEDIFF(MAX(DIM_CALENDAR_SALES[FSDATE]), adjustedDate, MONTH) = 1 , next,
        nextMonths))
        
    RETURN
        result

 Your help will be greatly appreciated! 

[lbendlin]

Please provide sample data that covers your issue or question completely, in a usable format (not as a screenshot).

Do not include sensitive information or anything not related to the issue or question.

If you are unsure how to upload data please refer to https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-...

Please show the expected outcome based on the sample data you provided.

Want faster answers? https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447...