cancel
Showing results for
Did you mean:
Frequent Visitor

## Sum items with latest date and time by categories and date

Hello, I have a complex problem i would like to get some help solving, I will explain as much as I can what the problems are, and I also included the project at the bottom of the post.

I have a table where each row is an item sale with a price and a stock value couting down for each sale.

What i want to do is to show a stock value ("stock after sales" with last date and time * "price") sorted by category in one table and by date in another. I'm trying to solve this by using DAX, but I cant seem to get the formulas correct when selecting only items with latest date and time per item.

The stock value should sum the stock for each item based on selected month, but should also include items sold before the selected month (item 1 was not sold in january but should still be calculated if that month is selected).

I have one table with sales and one with each uniqe item with categories with relation om itemID:

 ItemID Date Time stock after sales Price 1 2016-12-01 08:45:20 20 100 2 2016-12-02 10:15:55 5 200 3 2016-12-02 12:10:10 8 300 1 2016-12-03 10:38:53 19 100 4 2017-01-01 09:45:20 10 400 3 2017-01-01 10:20:55 7 300 6 2017-01-02 15:24:39 9 600 2 2017-01-03 13:20:55 4 200 2 2017-01-05 12:24:39 3 200 3 2017-02-01 09:08:27 6 300 6 2017-02-03 10:20:20 8 600 4 2017-02-05 12:10:39 9 400 4 2017-02-07 09:14:27 8 400 5 2017-02-08 10:01:55 5 500 5 2017-02-08 14:24:39 4 500

 ItemID Item group1 Item group2 1 Pants Boy 2 Pants Girl 3 Shirts Boy 4 Shirts Girl 5 Socks Boy 6 Socks Girl

"Sort by date" and "sort by item group" are the two tables where i want to show the current stock value, "Actual value tables show the correct values (when end date is 2017-02-08):

The columns in the tables are measures im currently using.

Problems:

• Stock dont sum "total" in "sort by item group" but the values are correct when no date filters are used
• when a different start date is used the values in "Stock by item group" are wrong, values before start date are not included.
• "sort by date" table sums all values, but i cant seem to sum only values with last date and time for each item.

I Included the example PBI file and excel on dropbox

https://www.dropbox.com/s/7wtw8bkkvuudua0/Problem%20DAX%20PowerBI.zip?dl=0

2 REPLIES 2
Microsoft

## Re: Sum items with latest date and time by categories and date

Hi @kalleeljas,

Althrough you have describe your problem in detail, I still need to verify the following information.

>> Problems: ort by item group: values dont sum i "total" on "Item group2" or "item group1"

In table "Sort by item group", how do you sort your data? You can click the button highlighted in yellow background. Please click the field which you want to use.

>> Sort By Date * Stock values should match "Actual values date" table.

Why the Sort By Date table should match “Actual values date” table? There is no relationship between them, they are different tables.

Best Regards,
Angelia

Frequent Visitor

## Re: Sum items with latest date and time by categories and date

Hello @v-huizhn-msft.

>> Problems: Sort by item group: values dont sum i "total" on "Item group2" or "item group1"

In table "Sort by item group", how do you sort your data? You can click the button highlighted in yellow background. Please click the field which you want to use.

I'm not sure what you mean "how do I sort my data"? the only sorting i do is alphabetical by item group2.

The table should look like this with totals, but the totals only shows the value with last date:

 Item group2 Item group1 Stock calc 5 Stock value 2 Boy Pants 19 1900 Shirts 6 1800 Socks 4 2000 Total 29 5700 Girl Pants 3 600 Shirts 8 3200 Socks 8 4800 Total 19 8600 Total 48 14300

Here are the calculations im curerntly using to get stock value:

`Stock value 2 = [Stock calc 5]*[Price calc]`
```Price calc = SUMX ( VALUES ( Tabell1[ItemID] );
CALCULATE ( SUM (Tabell1[Price] ); LASTDATE ( Tabell1[Date] );
FILTER( ALL(Tabell1) ; Tabell1[datetime] = MAX (Tabell1[datetime]))
)
)```
```Stock calc 5 = SUMX ( VALUES ( Tabell1[ItemID] );
CALCULATE ( SUM (Tabell1[stock after sales] ); LASTDATE ( Tabell1[Date] );
FILTER( ALL(Tabell1) ; Tabell1[datetime] = MAX (Tabell1[datetime]))
)
)```

>> Sort by date

There is no relationship between them, they are different tables.

The 3 "actual value" tables are just hardcoded tables to show the correct values, there should not be any "database" relation between them at all.

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!