cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
acanepa
Resolver I
Resolver I

LASTDATE by skuname

Hello,

 

I want to get lastdate but for each product

 

My DB is like this:

 

Date              | SKUNAME | Quantiy

01-01-2015   | Product 1   | 100

01-02-2015   | Product 1   | 50

01-03-2015   | Product 2   | 90

01-02-2015   | Product 3   | 40

01-01-2015   | Product 4   | 100

01-03-2015   | Product 1   | 400

01-04-2015   | Product 4   | 600

 

I want to receive for each row, the last day from the table. I wnat to obtain something similar to this:

 

Date              | SKUNAME | Last date    | Quantity

01-01-2015   | Product 1   | 01-03-2015  | 400

01-02-2015   | Product 1   | 01-03-2015  | 400

01-04-2015   | Product 2   | 01-04-2015  | 90

01-01-2015   | Product 3   | 01-08-2015  | 40

01-01-2015   | Product 4   | 01-09-2015  | 600

01-08-2015   | Product 1   | 01-08-2015  | 400

01-09-2015   | Product 4   | 01-08-2015  | 600

 

In need the last quantity of stock avaiable. I don't mind to have a distinct table like this

 

SKUNAME | Last date    | Quantity

 Product 1   | 01-03-2015  | 400

Product 2   | 01-04-2015  | 90

 Product 3   | 01-08-2015  | 40

 Product 4   | 01-08-2015  | 600

 

I have tried with CALCULATE(LASTDAY([DATE]),FILTER(SKUNAME)). This Formula gives the last day but of the whole table.

 

Regards,

 

14 REPLIES 14
Greg_Deckler
Super User
Super User

Did you create that with a measure? In theory, you *should* be able to create a measure of just "= LASTDATE([Date])" and having it in the table visualize should context filter it to the particular row it is in (should work in the last table you show) as it should filter it by your SKUNAME.


@ me in replies or I'll lose your thread!!!
Check out my External Tool for Power BI Desktop! Microsoft Hates Greg's Quick Measures
YouTube Channel! Microsoft Hates Greg
Check out my latest book!

@Greg_Deckler Power BI give the next error (is a summary of the error)

 

Error Message:

Error in the calculation of measure: It can't convert the value 'Product1' from type Text to type True/Flase

 

 

 

 

Is that with your formula or mine?


@ me in replies or I'll lose your thread!!!
Check out my External Tool for Power BI Desktop! Microsoft Hates Greg's Quick Measures
YouTube Channel! Microsoft Hates Greg
Check out my latest book!

@Greg_Deckler

 

It was from mine calculation. I made lastdate([date])  like a measure and also give me the last date from the whole table, so the last date is the same for all products

Hmm, I'll have to build a model and test it and get back to you.


@ me in replies or I'll lose your thread!!!
Check out my External Tool for Power BI Desktop! Microsoft Hates Greg's Quick Measures
YouTube Channel! Microsoft Hates Greg
Check out my latest book!

First, build a product dimension. You will run into issues and make your life harder if you insist on using only one table. The calculated column you want is just

 

LastDate =
LASTDATE( FactSale[Date] )

This is defined in the product dimension.

 

Some images of sample data and model.

 

My model - relationship between DimProduct and FactSale on SKUKeyMy model - relationship between DimProduct and FactSale on SKUKeyFactSale data - note the two products with last dates on 2016-01-02 and 2016-01-03.FactSale data - note the two products with last dates on 2016-01-02 and 2016-01-03.DimProduct, with the calculated column defined and behaving appropriately.DimProduct, with the calculated column defined and behaving appropriately.

@greggyb

 

Hi,

Thanks for your answer. I follow the same steps that you told me with the example data that you have provided and worked, but later I try with my own data and didn't work, even when the SKU Key is a number and have the exact same type of number: Whole Number.

I have the main table with columns:

date, sku_config, product_name,quantity,visible, skukey

 

An example column

01-12-15, FA492FB2J1EPKLACL, FA492FA1J1EPKLACL-7645455, Blusa Estilo Europeo, 1, 7645455

 

*The SKU key take the last 6 numbers of sku simple.

 

After that I generate a table with the formula:

 

Last Date Table= Distinct('BD'[Sku Key]

After that I created a column in that table with the formula

 

LD = LASTDATE('BD'[Date])

The column LD gives me the same date for all SKU Key: 07 of January

Regards,

greggyb
Resident Rockstar
Resident Rockstar

Have you created a relationship between your fact table and your product dimension with the fact table on the many side and the dimension on the one side?

@greggyb

Yes I create a relationship between Fact table (SkuKey) and DimProduct (SkuKey).

From various to one, from Dimproduct to Fact sales. (Unique).

greggyb
Resident Rockstar
Resident Rockstar


@acanepa wrote:

@greggyb

Yes I create a relationship between Fact table (SkuKey) and DimProduct (SkuKey).

From various to one, from Dimproduct to Fact sales. (Unique).


That last part is confusing to me. It sounds like DimProduct is on the many side of your relationship. Here's the relationship as I've defined it. Does yours look like this?

 

Note the * and 1 in the diagram view. Note the position of Fact on top and Relationship cardinality Many to oneNote the * and 1 in the diagram view. Note the position of Fact on top and Relationship cardinality Many to one

@greggyb

 

It is the same relationshiop.

greggyb
Resident Rockstar
Resident Rockstar

Are you able to share any sample data that reproduces this issue?

 

You can host a .pbix file on OneDrive or similar and share a public link with us.

 

I'm not able to get the same behavior you are.

I'd just like to follow up on this

 

I had same situation as seen on the first page. I needed to show different values with multiple dates only by last date. It was something like this:

 

Type                      Date
Regres                  1.2.2016
Regres                  2.2.2016
Regres                  3.2.2016
Salary Addition     3.2.2016
Salary Addition     4.2.2016
Salary Addition     5.2.2016
Bonus                    4.2.2016
Bonus                    5.2.2016
Bonus                    6.2.2016

 

And basically, I wanted to see this:

 

Regres                   3.2.2016
Salary Addition      5.2.2016
Bonus                     6.2.2016

 

At first I tried some DAX formulas, I thought FILTER(LASTDATE(EmployeeFinancialChanges[Date]); EmployeeFinancialChanges[Type]) or something like would work, but it didn't.

 

Then I tried what greggyb described - I created Dim-table with column LastDate= LASTDATE(EmployeeFinancialChanges[Date]), connected it to it's original Table (EmployeeFinancialChanges), but it didn't filter it out. I also had some trouble with type of connection in model. So I tried making simple Measure, as smoupre did, and it totally worked. I put "Type" and measure in table and everything was shown by last date.

 

@Greg_Deckler

 

It was from mine calculation. I made lastdate([date])  like a measure and also give me the last date from the whole table, so the last date is the same for all products

Helpful resources

Announcements
Power BI December 2021 Update_carousel 768x460.jpg

Check it Out!

Click here to read more about the December 2021 Updates!

User Group Leader Meeting January 768x460.png

Calling all User Group Leaders!

Don't miss the User Group Leader meetings on January, 24th & 25th, 2022.

Jan 2022 Dev Camp 768x460 copy.png

Power BI Dev Camp- January 27th, 2022

Mark your calendars and join us for our next Power BI Dev Camp!

Top Solution Authors
Top Kudoed Authors