Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
2 years ago
Solved

Rollover Function Needed Within Matrix

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 + Tot...
Need Help
Show and Tell
Tips and Tricks
Tutorial Requests
  • Jihwan_Kim's avatar
    Jihwan_Kim
    2 years ago

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

     

Jihwan_Kim's avatar
Jihwan_Kim
Super User
2 years ago

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.

  

Anonymous's avatar
Anonymous
Not applicable
2 years ago

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