Related Collumns or Summarize for create a table

[santigc97]

Hello,

I am having a problem with a new table that i want to create with the function related or summarize collumns

I have the following data on which for an item i have the DMD on each location and also on the collumns NEW and OLD i have the Item that will replace the current item on the future (both can be solled at the same time but at the end the old one will be consumed). Note that in my real report i will have more codes.

ITEM	LOC	DMD	NEW	OLD
99878	SPAIN	5	77898
77898	USA	-1011	55878
77898	SPAIN	4028	55878	99878
55878	SPAIN	20		77898

What i want is to make a new table or use a funcion for create new collumns that are the new items and old items with their demand (the new, new+1,new+2 with their demand and also the same for those that are old) in order to see in an easier way the different codes, their demands and also the total

Thanks for your help

[santigc97]

Hello,

It is almost correct but i have a problem with the number of the product, you are using a +1 or -1 but there are some cases in which the number are not consecutive (it was an example, i will update the table in the previous post), so you can use the data that we have bellow, how i should do for do like a filter that in real is like a vlookup/xlookup?

[v-jiewu-msft]

Hi @santigc97 ,

Based on my testing, please try the following methods:

1.Create the simple table.

2.Create the new columns to calculate.

NEW DMD = 
VAR _loc='Table'[LOC]
var _new='Table'[NEW]
return CALCULATE(SUM('Table'[DMD]),FILTER(ALL('Table'),'Table'[LOC]=_loc&&'Table'[ITEM]=_new))

new+1 = 
VAR ite = 'Table'[ITEM]
VAR loc = 'Table'[LOC]
RETURN
IF(('Table'[NEW] + 1) <= MAX('Table'[ITEM]) && 'Table'[NEW] <> BLANK(), 'Table'[NEW] + 1, BLANK())

NEW+1 DMD = 
VAR _loc = 'Table'[LOC]
var _new = 'Table'[new+1]
return CALCULATE(SUM('Table'[DMD]),FILTER(ALL('Table'),'Table'[LOC]=_loc &&'Table'[ITEM] = _new))

OLD DMD = 
VAR _loc = 'Table'[LOC]
var _old = 'Table'[OLD]
return CALCULATE(SUM('Table'[DMD]),FILTER(ALL('Table'),'Table'[LOC]=_loc&&'Table'[ITEM]= _old))

OLD - 1 = 
VAR loc = 'Table'[LOC]
VAR old = 'Table'[OLD]
var _item = CALCULATE(MAX('Table'[ITEM]),FILTER(ALL('Table'),'Table'[LOC]= loc &&'Table'[ITEM] = old - 1))
return 
IF(NOT(ISBLANK('Table'[OLD]))&&NOT(ISBLANK(_item)),'Table'[OLD]-1)

3.The result is shown below.

Best Regards,

Wisdom Wu

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