Forum Discussion
Rollover Function Needed Within Matrix
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] ) )
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] )
)
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
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.
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])
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!!
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