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

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
Super User IV
Super User IV

Re: LASTDATE by skuname

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.


I have book! Learn Power BI from Packt


Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!

acanepa Member
Member

Re: LASTDATE by skuname

@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

 

 

 

 

Super User IV
Super User IV

Re: LASTDATE by skuname

Is that with your formula or mine?


I have book! Learn Power BI from Packt


Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!

acanepa Member
Member

Re: LASTDATE by skuname

@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

acanepa Member
Member

Re: LASTDATE by skuname

@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

Super User IV
Super User IV

Re: LASTDATE by skuname

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


I have book! Learn Power BI from Packt


Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!

Highlighted
greggyb New Contributor
New Contributor

Re: LASTDATE by skuname

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.

 

Capture.JPGMy model - relationship between DimProduct and FactSale on SKUKeyCapture-1.JPGFactSale data - note the two products with last dates on 2016-01-02 and 2016-01-03.Capture-2.JPGDimProduct, with the calculated column defined and behaving appropriately.

acanepa Member
Member

Re: LASTDATE by skuname

@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 New Contributor
New Contributor

Re: LASTDATE by skuname

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?

Helpful resources

Announcements
Coming Soon: T-Shirt Design Contest

Coming Soon: T-Shirt Design Contest

Keep your eyes open for our upcoming T-shirt design contest!

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
Top Kudoed Authors