Sum items with latest date and time by categories and date

[kalleeljas]

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

Thank you for your time.

[v-huizhn-msft]

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

[kalleeljas]

Hello @v-huizhn-msft.

Thank you for your response.

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