Sum Product by Family, Category and Product with time difference

[DavidGTMS]

Hello everyone,

I have a problem and I do not know how to solve it. I have a table with different products and families. (see screenshot)

I have families, categories, products, date and sales. I receive updated data at 8 AM and at 16 PM. Each family has the same categories as the other families. But each family, sells different products. The goal is to get the difference by product between the oldest and the most recent time. Then add the sales of the 2 or more products of each category and also find out the difference.

Thank you.

[v-qiuyu-msft]

Hi @DavidGTMS,

You can create calculated columns below: 

Difference = var MinD=CALCULATE(MIN('Table1'[Date]),ALLEXCEPT(Table1,Table1[Familiy],'Table1'[Category],Table1[Product]))
var MaxD=CALCULATE(MAX('Table1'[Date]),ALLEXCEPT(Table1,Table1[Familiy],'Table1'[Category],Table1[Product]))
return 
IF('Table1'[Date]=MaxD,
LOOKUPVALUE('Table1'[Sold],Table1[Familiy],'Table1'[Familiy],'Table1'[Category],'Table1'[Category],'Table1'[Product],'Table1'[Product],'Table1'[Date],MaxD)-LOOKUPVALUE('Table1'[Sold],Table1[Familiy],'Table1'[Familiy],'Table1'[Category],'Table1'[Category],'Table1'[Product],'Table1'[Product],'Table1'[Date],MinD),BLANK())

Ultimate Acumulate Sold = 
var MaxD=CALCULATE(MAX('Table1'[Date]),ALLEXCEPT(Table1,Table1[Familiy],'Table1'[Category],Table1[Product]))
return 
IF('Table1'[Date]=MaxD && 'Table1'[Product]=CALCULATE(MAX('Table1'[Product]),ALLEXCEPT('Table1','Table1'[Familiy],'Table1'[Category])),
CALCULATE(SUM('Table1'[Sold]),FILTER('Table1','Table1'[Familiy]=EARLIER(Table1[Familiy]) && 'Table1'[Category]=EARLIER(Table1[Category]) && 'Table1'[Date]=MaxD)),BLANK())

Prev. Acumulate Sold = 
var MinD=CALCULATE(Min('Table1'[Date]),ALLEXCEPT(Table1,Table1[Familiy],'Table1'[Category],Table1[Product]))
var MaxD=CALCULATE(Max('Table1'[Date]),ALLEXCEPT(Table1,Table1[Familiy],'Table1'[Category],Table1[Product]))
return 
IF('Table1'[Date]=MaxD && 'Table1'[Product]=CALCULATE(Max('Table1'[Product]),ALLEXCEPT('Table1','Table1'[Familiy],'Table1'[Category])),
CALCULATE(SUM('Table1'[Sold]),FILTER('Table1','Table1'[Familiy]=EARLIER(Table1[Familiy]) && 'Table1'[Category]=EARLIER(Table1[Category]) && 'Table1'[Date]=MinD)),BLANK())

DifferenceNew = [Ultimate Acumulate Sold]-[Prev. Acumulate Sold]

Best Regards,
Qiuyun Yu 

[DavidGTMS]

Hi v-qiuyu-msft,

Thank you very much for your quick response. I'm sorry to tell you, I send you my query badly. I send you an updated screenshot.

I do not know how to attach the excel file, so I stick the updated table.

Thank you very much again.

Family	Category	Product	Date	Total Sold	Difference Daily	Ultimate Acumulate Sold	Prev. Acumulate Sold	Difference Acumulate Sold
Adams	A	1	01/01/2018 8:00	300
Adams	A	1	01/01/2018 16:00	355	55		355
Adams	A	1	02/01/2018 8:00	400
Adams	A	1	02/01/2018 16:00	600	200	600		245
Adams	A	2	01/01/2018 8:00	500
Adams	A	2	01/01/2018 16:00	525	25		525
Adams	A	2	02/01/2018 8:00	550
Adams	A	2	02/01/2018 16:00	605	55	605		80
Adams	B	1	01/01/2018 8:00	450
Adams	B	1	01/01/2018 16:00	538	88		538
Adams	B	1	02/01/2018 8:00	550
Adams	B	1	02/01/2018 16:00	605	55	605		67
Adams	B	2	01/01/2018 8:00	10
Adams	B	2	01/01/2018 16:00	12	2		12
Adams	B	2	02/01/2018 8:00	14
Adams	B	2	02/01/2018 16:00	20	6	20		8
Benjamin	A	3	01/01/2018 8:00	10
Benjamin	A	3	01/01/2018 16:00	22	12		22
Benjamin	A	3	02/01/2018 8:00	23
Benjamin	A	3	02/01/2018 16:00	33	10	33		11
Benjamin	A	4	01/01/2018 8:00	30
Benjamin	A	4	01/01/2018 16:00	41	11		41
Benjamin	A	4	02/01/2018 8:00	41
Benjamin	A	4	02/01/2018 16:00	49	8	49		8
Benjamin	B	3	01/01/2018 8:00	100
Benjamin	B	3	01/01/2018 16:00	102	2		102
Benjamin	B	3	02/01/2018 8:00	102
Benjamin	B	3	02/01/2018 16:00	102	0	102		0
Benjamin	B	4	01/01/2018 8:00	30
Benjamin	B	4	01/01/2018 16:00	32	2		32
Benjamin	B	4	02/01/2018 8:00	33
Benjamin	B	4	02/01/2018 16:00	33	0	33		1

[v-qiuyu-msft · Accepted solution]

Hi @DavidGTMS,

You can create calculated columns below: 

Datenew = DATEVALUE(FORMAT(Table1[date],"MM-DD-YYYY"))

Difference Daily = var maxd= CALCULATE(MAX('Table1'[Date]),ALLEXCEPT(Table1,'Table1'[Family],Table1[Category],Table1[Product],Table1[Datenew]))
var mind= CALCULATE(Min('Table1'[Date]),ALLEXCEPT(Table1,'Table1'[Family],Table1[Category],Table1[Product],Table1[Datenew]))
return 
IF('Table1'[Date]=maxd,LOOKUPVALUE('Table1'[Total Sold],Table1[Family],'Table1'[Family],'Table1'[Category],'Table1'[Category],'Table1'[Product],'Table1'[Product],'Table1'[Date],MaxD)-LOOKUPVALUE('Table1'[Total Sold],Table1[Family],'Table1'[Family],'Table1'[Category],'Table1'[Category],'Table1'[Product],'Table1'[Product],'Table1'[Date],mind),BLANK())

Ultimate Acumulate Sold = var m=CALCULATE(MAX('Table1'[Date]),ALLEXCEPT(Table1,'Table1'[Family],Table1[Category],Table1[Product]))
    return 
   IF('Table1'[Date]=m, LOOKUPVALUE(Table1[Total Sold],'Table1'[Family],'Table1'[Family],Table1[Category],Table1[Category],Table1[Product],Table1[Product],Table1[Date],m),BLANK())

Prev. Acumulate Sold = var maxd= CALCULATE(MAX('Table1'[Date]),ALLEXCEPT(Table1,'Table1'[Family],Table1[Category],Table1[Product],Table1[Datenew]))
var md=CALCULATE(MAX('Table1'[Date]),ALLEXCEPT(Table1,'Table1'[Family],Table1[Category],Table1[Product]))
return 
IF('Table1'[Date]=maxd && 'Table1'[Date]<>md,LOOKUPVALUE('Table1'[Total Sold],Table1[Family],'Table1'[Family],'Table1'[Category],'Table1'[Category],'Table1'[Product],'Table1'[Product],'Table1'[Date],MaxD))

Difference Acumulate Sold = IF('Table1'[Ultimate Acumulate Sold]<>BLANK(),'Table1'[Ultimate Acumulate Sold]- CALCULATE(MAX('Table1'[Prev. Acumulate Sold]),ALLEXCEPT(Table1,'Table1'[Family],Table1[Category],Table1[Product])),BLANK())

Best Regards,
Qiuyun Yu 

[DavidGTMS]

Thank you.

Good joob.

Best regards,