cancel
Showing results for
Did you mean:
Highlighted
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)

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

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

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.
4 REPLIES 4
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]`

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.
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.

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

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.
Regular Visitor

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

Thank you.

Good joob.

Best regards,

Announcements

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?

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

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