cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
kalleeljas Frequent Visitor
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:

ItemIDDateTimestock after salesPrice
12016-12-0108:45:2020100
22016-12-0210:15:555200
32016-12-0212:10:108300
12016-12-0310:38:5319100
42017-01-0109:45:2010400
32017-01-0110:20:557300
62017-01-0215:24:399600
22017-01-0313:20:554200
22017-01-0512:24:393200
32017-02-0109:08:276300
62017-02-0310:20:208600
42017-02-0512:10:399400
42017-02-0709:14:278400
52017-02-0810:01:555500
52017-02-0814:24:394500

 

 

ItemIDItem group1Item group2
1PantsBoy
2PantsGirl
3ShirtsBoy
4ShirtsGirl
5SocksBoy
6SocksGirl

 

"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):

bild.png

 

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.

2 REPLIES 2
Microsoft v-huizhn-msft
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

kalleeljas Frequent Visitor
Frequent Visitor

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

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 group2Item group1Stock calc 5Stock value 2
BoyPants191900
 Shirts61800
 Socks42000
 Total295700
GirlPants3600
 Shirts83200
 Socks84800
 Total198600
Total 4814300

 

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.

 

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