cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
DavidGTMS Regular Visitor
Regular Visitor

Sum Product by Family, Category and Product with time difference

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)

 

image.png

 

 

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.

1 ACCEPTED SOLUTION

Accepted Solutions
Moderator v-qiuyu-msft
Moderator

Re: Sum Product by Family, Category and Product with time difference

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())

 

q4.PNG

 

 

Best Regards,
Qiuyun Yu 

Community Support Team _ Qiuyun Yu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

4 REPLIES 4
Moderator v-qiuyu-msft
Moderator

Re: Sum Product by Family, Category and Product with time difference

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]

q2.PNG

 

 

 

Best Regards,
Qiuyun Yu 

Community Support Team _ Qiuyun Yu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
DavidGTMS Regular Visitor
Regular Visitor

Re: Sum Product by Family, Category and Product with time difference

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.

 

FamilyCategoryProductDateTotal SoldDifference DailyUltimate Acumulate SoldPrev. Acumulate SoldDifference Acumulate Sold
AdamsA101/01/2018 8:00300    
AdamsA101/01/2018 16:0035555 355 
AdamsA102/01/2018 8:00400    
AdamsA102/01/2018 16:00600200600 245
AdamsA201/01/2018 8:00500    
AdamsA201/01/2018 16:0052525 525 
AdamsA202/01/2018 8:00550    
AdamsA202/01/2018 16:0060555605 80
AdamsB101/01/2018 8:00450    
AdamsB101/01/2018 16:0053888 538 
AdamsB102/01/2018 8:00550    
AdamsB102/01/2018 16:0060555605 67
AdamsB201/01/2018 8:0010    
AdamsB201/01/2018 16:00122 12 
AdamsB202/01/2018 8:0014    
AdamsB202/01/2018 16:0020620 8
BenjaminA301/01/2018 8:0010    
BenjaminA301/01/2018 16:002212 22 
BenjaminA302/01/2018 8:0023    
BenjaminA302/01/2018 16:00331033 11
BenjaminA401/01/2018 8:0030    
BenjaminA401/01/2018 16:004111 41 
BenjaminA402/01/2018 8:0041    
BenjaminA402/01/2018 16:0049849 8
BenjaminB301/01/2018 8:00100    
BenjaminB301/01/2018 16:001022 102 
BenjaminB302/01/2018 8:00102    
BenjaminB302/01/2018 16:001020102 0
BenjaminB401/01/2018 8:0030    
BenjaminB401/01/2018 16:00322 32 
BenjaminB402/01/2018 8:0033    
BenjaminB402/01/2018 16:0033033 1

 

image.png

Moderator v-qiuyu-msft
Moderator

Re: Sum Product by Family, Category and Product with time difference

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())

 

q4.PNG

 

 

Best Regards,
Qiuyun Yu 

Community Support Team _ Qiuyun Yu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

DavidGTMS Regular Visitor
Regular Visitor

Re: Sum Product by Family, Category and Product with time difference

Thank you.

 

Good joob.

 

Best regards,

Helpful resources

Announcements
Meet the 2020 Season 1 Power BI Super Users!

Meet the 2020 Season 1 Power BI Super Users!

It’s the start of a new Super User season! Learn all about the new Super Users and brand-new tiered recognition system.

Super User Challenge: Can You Solve These?

Super User Challenge: Can You Solve These?

We're celebrating the start of the New Super User season with our first ever Super User 'Can You Solve These?' challenge.

Power BI Desktop Update - February 2020

Power BI Desktop Update - February 2020

We are super excited for our update this month, as we are releasing two of our top community requests!

Power Platform Online Conference

Power Platform Online Conference

Join us for the first ever Power Platform Online Conference!

Top Solution Authors