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

     

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.

  

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

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

    • Anonymous's avatar
      Anonymous
      Not applicable
      2 years ago

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

  • 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