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 + 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!

 

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

     

6 Replies

  • Jihwan_Kim's avatar
    Jihwan_Kim
    Super User
    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] )
    )

     

    • Anonymous's avatar
      Anonymous
      Not applicable
      2 years ago

      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'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.