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

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