How to summarize the table based on the dynamic slicer of N of week before and N of week after

[naoyixue1]

Hey all,

I'm working on ABC inventory anaylsis based on the actualized ship and future forecast. To give the team availability to check data based on different time range from the current week, i want to append ship and forecast table accordingly based on the N of "weeks before" and "week after" selection. See below example

Assumption: we are now in the week 2024026 highlighted in red as below

Shipment table: Based on the slicer i have, I want to pull 5 weeks before data based on the current week ( highlighted in green)

Forecast table:  Based on the slicer i have, I want to pull 6 weeks after data based on the current week( highlighted in green)

Then I can use 'union function' dax to append those two tables together to do my ABC anaylsis.  My question is how I can create the virtual tables based on the slicer i have for both shipment table and forecast table and append them together. 

Thanks folks!

[Anonymous · Accepted solution]

Hi @naoyixue1 ,

The Table data is shown below:

Please follow these steps:

1.Use the following DAX expression to create a column in table 'Shipment Table'

Column = ABS([Week Before])

2.Adding fields to the slicer

3.Use the following DAX expression to create a table

Table = DISTINCT(UNION(VALUES('Forecast Table'[Week ]),VALUES('Shipment Table'[Week])))

4.Use the following DAX expression to create measures

_Value = 
VAR _slicerForWeekAfter = SELECTEDVALUE('Forecast Table'[Week After])
VAR _slicerForWeekBefore = SELECTEDVALUE('Shipment Table'[Week Before])
VAR _shipmentTable = SELECTCOLUMNS(FILTER(ALL('Shipment Table'),'Shipment Table'[Week Before] < 0 && 'Shipment Table'[Week Before] >= _slicerForWeekBefore),
"Week",[Week],"Value",[Value],"Week Filter",[Week Before])
VAR _ForecastTable = SELECTCOLUMNS(FILTER(ALL('Forecast Table'),'Forecast Table'[Week After] >= 0  && 'Forecast Table'[Week After] <= _slicerForWeekAfter),
"Week",[Week ],"Value",[Value],"Week Filter",[Week After])
VAR _table = UNION(_shipmentTable,_ForecastTable)
VAR _week = SELECTEDVALUE('Table'[Week ])

RETURN MAXX(FILTER(_table,[Week] = _week),[Value])

_WeekFilter = 
VAR _slicerForWeekAfter = SELECTEDVALUE('Forecast Table'[Week After])
VAR _slicerForWeekBefore = SELECTEDVALUE('Shipment Table'[Week Before])
VAR _shipmentTable = SELECTCOLUMNS(FILTER(ALL('Shipment Table'),'Shipment Table'[Week Before] < 0 && 'Shipment Table'[Week Before] >= _slicerForWeekBefore),
"Week",[Week],"Value",[Value],"Week Filter",[Week Before])
VAR _ForecastTable = SELECTCOLUMNS(FILTER(ALL('Forecast Table'),'Forecast Table'[Week After] >= 0  && 'Forecast Table'[Week After] <= _slicerForWeekAfter),
"Week",[Week ],"Value",[Value],"Week Filter",[Week After])
VAR _table = UNION(_shipmentTable,_ForecastTable)
VAR _week = SELECTEDVALUE('Table'[Week ])

RETURN MAXX(FILTER(_table,[Week] = _week),[Week Filter])

5.Final output

Best Regards,
Wenbin Zhou
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[naoyixue1]

@Anonymous Sorry, I have a follow up question about this table. 

It seems I have to add the week from maintable there in the metrics, then I can see the value. If I move the week there, then nothing will show up. (see below)

With Week from Main table

Without Week from Main table

Since I want to identify ABC ranking based on the cumulative units only at item level, is there any way for me to see only Item and related units based on the selected weeks before and weeks after. So I then can add the measure to calculate cumulative units to finally assign A,B,and C ranking based on that dynamic table. 

Again, thank you so much, WenBin!

[Anonymous]

Hi @naoyixue1 ,

Regarding your question, since column 'Item' and column 'Week' have a filtering effect in the DAX expression, they both must be present to filter the data effectively.

[naoyixue1]

@Anonymous Is there any way for me to create a virtual table to only pull the item and value based on the selected week? Or do you have any suggestion. 

[naoyixue1]

@Anonymous Hey Wenbin, I found the real problem is a little bit complicated than what i described above. In the real world,i want to show both item and week in the final table. Is that ok for me to use 'UNION' dax to combine those two tables, instead of the Week columns of those two tables. (see below) But after I tried, the table is there. 

Also I forgot to mention we might have both forecast and ship in the same week at the same item. Given that, I want to make the comparison between ship and forecast at item level to pick the bigger one. How should I implement that kind of thoughts before I append two separate table together? (see below, we are now in the week 26, so week is 2024026) 

Ship Table

Fcst Table

Again, really appreciate your efforts!

[Anonymous]

Hi @naoyixue1 ,

The order of the columns in the two tables is inconsistent, and using the 'UNION' function will result in data errors.

The Table data is shown below:

Please follow these steps:

1.Use the following DAX expression to create a column in table 'Forecast Table'

Column = 
VAR _valueFromShipmentTable = LOOKUPVALUE('Shipment Table'[Value],'Shipment Table'[Item],[Item],'Shipment Table'[Week],[Week])
RETURN MAX([Value],_valueFromShipmentTable)

2.Use the following DAX expression to create a table

Table = 
VAR _ShipmentTable = SELECTCOLUMNS('Shipment Table',"Item",[Item],"Week",[Week])
VAR _ForecastTable = SELECTCOLUMNS('Forecast Table',"Item",[Item],"Week",[Week])
RETURN DISTINCT(UNION(_ShipmentTable,_ForecastTable))

3.Use the following DAX expression to create measures

_Value = 
VAR _slicerForWeekAfter = SELECTEDVALUE('Forecast Table'[Week After])
VAR _slicerForWeekBefore = SELECTEDVALUE('Shipment Table'[Week Before])
VAR _shipmentTable = SELECTCOLUMNS(FILTER(ALL('Shipment Table'),'Shipment Table'[Week Before] <= 0 && 'Shipment Table'[Week Before] >= _slicerForWeekBefore),
"Week",[Week],"Value",[Value],"Week Filter",[Week Before],"Item",[Item])
VAR _ForecastTable = SELECTCOLUMNS(FILTER(ALL('Forecast Table'),'Forecast Table'[Week After] >= 0  && 'Forecast Table'[Week After] <= _slicerForWeekAfter),
"Week",[Week],"Value",[Column],"Week Filter",[Week After],"Item",[Item])
VAR _table = UNION(_shipmentTable,_ForecastTable)
VAR _week = SELECTEDVALUE('Table'[Week])
VAR _Item = SELECTEDVALUE('Table'[Item])
RETURN MAXX(FILTER(_table,[Week] = _week && [Item] = _Item),[Value])

_WeekFilter = 
VAR _slicerForWeekAfter = SELECTEDVALUE('Forecast Table'[Week After])
VAR _slicerForWeekBefore = SELECTEDVALUE('Shipment Table'[Week Before])
VAR _shipmentTable = SELECTCOLUMNS(FILTER(ALL('Shipment Table'),'Shipment Table'[Week Before] <= 0 && 'Shipment Table'[Week Before] >= _slicerForWeekBefore),
"Week",[Week],"Value",[Value],"Week Filter",[Week Before],"Item",[Item])
VAR _ForecastTable = SELECTCOLUMNS(FILTER(ALL('Forecast Table'),'Forecast Table'[Week After] >= 0  && 'Forecast Table'[Week After] <= _slicerForWeekAfter),
"Week",[Week],"Value",[Column],"Week Filter",[Week After],"Item",[Item])
VAR _table = UNION(_shipmentTable,_ForecastTable)
VAR _week = SELECTEDVALUE('Table'[Week])
VAR _Item = SELECTEDVALUE('Table'[Item])
RETURN MAXX(FILTER(_table,[Week] = _week && [Item] = _Item),[Week Filter])

4.Final output

Best Regards,
Wenbin Zhou
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[naoyixue1]

@Anonymous Really appreciate your help. But I still ran into two issues or questions.

1: Try to understand the purpose of the max funtion at the bottom for both value and week filter calculation, which is the last step of each measure. Can you explain more about that? 

2:  It reports an error of week filter measure

But the value of all week difference i'm using is the integer. (see below), So I don't know what happened. 

Shipment Table - Week After (to make it easy when mereging two table(shipment and forecast), i put the same column name as 'Week after', althought it technically should be 'Week before' in this table 'Week before' , comparing with current week. 

Forecast Table - Week After

Reference table  Forward Looking table Forwardlooking week Column Selection.

Reference table  Backward Looking table Backwardlooking week Column Selection.

Again, thank you so much, WenBin!

[Anonymous]

Hi @naoyixue1 ,

First of all, let's make it clear that your requirement is to dynamically generate table data based on the selection in the slicer.Only measures are dynamically calculated based on slicer selections. Calculated columns and calculated tables are not.

This part is to use variables to store virtual tables in measure.(You can think of it as a calculation table that will dynamically change the value based on the slicer)

This step is to create filter conditions for the measure.

Since there is no relationship between the table we created and the source table, 'Item' and 'Week' have no filtering effect on the measure .So we use the 'Selectvalue' function to get the values ​​of 'Item' and 'Week' in each row, and use 'Filter' to filter.Finally use the 'MAXX' table function to get the filtered value

Second question.

Check if the data type is 'Text'

If the problem persists, can you share the .pbix file without sensitive data?

Best Regards,
Wenbin Zhou
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[naoyixue1]

@Anonymous I think i understand the steps you described above, but i still have the same issue as before. See pbix.file as attached. https://1drv.ms/u/s!AobyozIQwx-9k5wd_S6qCOV1msvnAA?e=5XyPSD 

Thanks a lot in advance!

[Anonymous]

Hi @naoyixue1 ,

The problem should come from here.

I modified the dax expression slightly, please refer to the .PBIX file for details.

Best Regards,
Wenbin Zhou
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous · Accepted solution]

Hi @naoyixue1 ,

The Table data is shown below:

Please follow these steps:

1.Use the following DAX expression to create a column in table 'Shipment Table'

Column = ABS([Week Before])

2.Adding fields to the slicer

3.Use the following DAX expression to create a table

Table = DISTINCT(UNION(VALUES('Forecast Table'[Week ]),VALUES('Shipment Table'[Week])))

4.Use the following DAX expression to create measures

_Value = 
VAR _slicerForWeekAfter = SELECTEDVALUE('Forecast Table'[Week After])
VAR _slicerForWeekBefore = SELECTEDVALUE('Shipment Table'[Week Before])
VAR _shipmentTable = SELECTCOLUMNS(FILTER(ALL('Shipment Table'),'Shipment Table'[Week Before] < 0 && 'Shipment Table'[Week Before] >= _slicerForWeekBefore),
"Week",[Week],"Value",[Value],"Week Filter",[Week Before])
VAR _ForecastTable = SELECTCOLUMNS(FILTER(ALL('Forecast Table'),'Forecast Table'[Week After] >= 0  && 'Forecast Table'[Week After] <= _slicerForWeekAfter),
"Week",[Week ],"Value",[Value],"Week Filter",[Week After])
VAR _table = UNION(_shipmentTable,_ForecastTable)
VAR _week = SELECTEDVALUE('Table'[Week ])

RETURN MAXX(FILTER(_table,[Week] = _week),[Value])

_WeekFilter = 
VAR _slicerForWeekAfter = SELECTEDVALUE('Forecast Table'[Week After])
VAR _slicerForWeekBefore = SELECTEDVALUE('Shipment Table'[Week Before])
VAR _shipmentTable = SELECTCOLUMNS(FILTER(ALL('Shipment Table'),'Shipment Table'[Week Before] < 0 && 'Shipment Table'[Week Before] >= _slicerForWeekBefore),
"Week",[Week],"Value",[Value],"Week Filter",[Week Before])
VAR _ForecastTable = SELECTCOLUMNS(FILTER(ALL('Forecast Table'),'Forecast Table'[Week After] >= 0  && 'Forecast Table'[Week After] <= _slicerForWeekAfter),
"Week",[Week ],"Value",[Value],"Week Filter",[Week After])
VAR _table = UNION(_shipmentTable,_ForecastTable)
VAR _week = SELECTEDVALUE('Table'[Week ])

RETURN MAXX(FILTER(_table,[Week] = _week),[Week Filter])

5.Final output

Best Regards,
Wenbin Zhou
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[naoyixue1 · Accepted solution]

@Anonymous It works correctly. Thanks Wen Bin!

[naoyixue1]

Thanks Wenbin. Let me try and keep you posted. But again, really appreciate that.