Rollover Function Needed Within Matrix

[marksaba]

Hello

I have a table with the below metrics. Stock On Hand is always the current month, then Total Receipts and Total Demand for each month. Net Inventory is a measure that is Stock On Hand + Total Receipts - Total Demand. 

I would like a new Stock On Hand formula that takes the previous month's Net Inventory as the starting Stock On Hand, unless there is a Stock on Hand listed (Current month). Therefore, the first column is correct. Jan 2024 Stock on Hand would be 1,464,772. Jan 2024 Net Inventory would be 1,464,772+346,766-207,748. Then, Feb 2024's Stock on Hand would be that Jan 2024 Net Inventory and so on.

Each Month has a sort number, which is below it. I have attempted building a function that takes the Sort Number minus 1 to retrieve the last month's Net Inventory to no avail. Can someone help me build out this function?

Thank you!

[Jihwan_Kim · Accepted solution]

Hi,

I am not sure how your semantic model looks like, but I tried to create a sample pbix file like below.

Please check the below picture and the attached pbix file.

I hope the below can provide some ideas on how to create a solution for your semantic model.

Total receipts: = 
SUM( Data[receipt] )

Total demand: = 
SUM( Data[demand] )

WINDOW function (DAX) - DAX | Microsoft Learn

Net inventory: = 
VAR _t =
    ADDCOLUMNS (
        WINDOW (
            1,
            ABS,
            0,
            REL,
            ALL ( 'Calendar'[Month Year], 'Calendar'[Month Year sort] ),
            ORDERBY ( 'Calendar'[Month Year sort], ASC )
        ),
        "@dec2023",
            CALCULATE (
                SUM ( Data[stockonhand] ),
                KEEPFILTERS ( 'Calendar'[Month Year sort] = DATE ( 2023, 12, 31 ) )
            ),
        "@receipt", [Total receipts:],
        "@demand", [Total demand:]
    )
RETURN
    SUMX ( _t, [@dec2023] + [@receipt] - [@demand] )

Stock on hand: = 
VAR _t =
    ADDCOLUMNS (
        WINDOW (
            1,
            ABS,
            -1,
            REL,
            ALL ( 'Calendar'[Month Year], 'Calendar'[Month Year sort] ),
            ORDERBY ( 'Calendar'[Month Year sort], ASC )
        ),
        "@dec2023",
            CALCULATE (
                SUM ( Data[stockonhand] ),
                KEEPFILTERS ( 'Calendar'[Month Year sort] = DATE ( 2023, 12, 31 ) )
            ),
        "@receipt", [Total receipts:],
        "@demand", [Total demand:]
    )
RETURN
    IF (
        SELECTEDVALUE ( 'Calendar'[Month Year sort] ) = DATE ( 2023, 12, 31 ),
        CALCULATE (
            SUM ( Data[stockonhand] ),
            KEEPFILTERS ( 'Calendar'[Month Year sort] = DATE ( 2023, 12, 31 ) )
        ),
        SUMX ( _t, [@dec2023] + [@receipt] - [@demand] )
    )

[marksaba]

Thank you! Dec 2023 won't always be the current month; I would like to use a TODAY () function to reference the current month. How can this be adjusted using a TODAY() function instead of referencing Dec 2023? Thanks

[Jihwan_Kim]

Hi,

in the measure that I wrote, DATE ( 2023, 12, 31 ) is hardcoded.

please try to replace it with EOMONTH (today(),0)
I hope this works.

Thank you.

[marksaba]

Hi Jihwan-

This measure has worked great. However, I now realized I need to factor for negatives. Below is the function for Net Inventory WALK as shown on the table:

Net Inventory WALK = 
VAR _t = 
ADDCOLUMNS(
    WINDOW(
        1,
        ABS,
        0,
        REL,
        ALL('Date Table'[Month Yr Sorted],'Date Table'[Sort]),
        ORDERBY('Date Table'[Sort],ASC)
    ),
    "@currentmonth",
    CALCULATE(
        [Stock On Hand],
        KEEPFILTERS('Date Table'[End of Month]=EOMONTH(TODAY(),0))
    ),
    "@receipt",[Total Receipts],
    "@demand",[Total Demand]
)
RETURN
SUMX(_t,[@currentmonth]+[@receipt]-[@demand])

However, my goal is to represent the Net Inventory WALK value as shown in the highlighted bottom row of the table (named "Desired Net Inventory WALK"). In this adjustment, it disregards when the Net Inventory for a month is Negative and makes the starting inventory for the next month 0. Therefore, Net Inventory for Jan-May 2024 should each be 0, then Jun 2024 at 799, etc (as shown in Desired Net Inventory WALK).

Do you know how to accomplish this?

Thank you

[marksaba]

So close to working but I believe that isn't working because the Month Yr column is a Text format, which it needs to be. I replaced DATE ( 2023, 12, 31 ) to be KEEPFILTERS('Date Table'[Sort]=12) just to see if this works, which it does. However, this only works during this month because Dec 2023 is sorted as #12. When Jan 2024 comes around and is sorted as #13, it will not work. I have a formula that takes the TODAY() function and looks up to find the sort. This formula works and returns 12:

TodaySort = LOOKUPVALUE('Date Table'[Sort],'Date Table'[Month Yr Sorted],FORMAT(TODAY(),"MMM YYYY"))

However, when I try to make that part of the larger function equal to the TodaySort measure, the table cannot calculate. 

KEEPFILTERS('Date Table'[Sort]=[TodaySort])

I'm confused on how to resolve this. 

This works:

KEEPFILTERS('Date Table'[Sort]=12)

This equals 12:

TodaySort = LOOKUPVALUE('Date Table'[Sort],'Date Table'[Month Yr Sorted],FORMAT(TODAY(),"MMM YYYY"))

Yet, this does not work:

KEEPFILTERS('Date Table'[Sort]=[TodaySort])

[marksaba]

I actually was able to resolve this by adding a column in my data table that formats the month yr value as a Date. I then referenced that column. Appreciate your help!!