LASTDATE by skuname

[acanepa]

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,

[Greg_Deckler]

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.

[acanepa]

@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

[Greg_Deckler]

Is that with your formula or mine?

[acanepa]

@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

[Greg_Deckler]

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

[greggyb]

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

[acanepa]

@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]

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?

[acanepa]

@greggyb

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

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

[greggyb]

---

@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 one

[acanepa]

@greggyb

It is the same relationshiop.

[greggyb]

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.

[smatesic]

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.

[acanepa]

@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