Last Price

[neobaron]

Hello everyone,

I would like to get you advice on few issues I am having with powerpivot DAX please.

I have several data from diverse product. Each product change its price one or more times per Month (normally one) and not all months. What i need is get a pivot table where i can see a continuous segment from the first effective price until the last one.

As you can see in the image i have space between them. What i need is that if the month don´t have a new price take the last one. I´ve checked with different DAX (lastnonblan, lastempty, datesbetween...) but i think that i don´t have enough knowledge for determine which is the correct one.

This is the formula i have running right now:

TEST:=If(AVERAGE(Combo[Price])=0,CALCULATE(SUM(Combo[Unit Price]),LASTNONBLANK('Date'[Date],COUNTROWS(RELATEDTABLE(Combo)))), AVERAGE([Unit Price]))

I have two tables:
-One where i have data. New prices and the date when were effective and the product.
-Other is a simple DATE table.

Any suggestion? Thank you guys, i read Marco Russo & Alberto Ferrari (Bulding Data Models wiht Powerpivot) but i did´t find a solution.

Thank you in advance for your support and help, it really help me!

 

[ImkeF]

Hi,
have a look here:
Kasper de Jonge PowerPivot and Power BI Blog | Solving a business problem with Powerpivot CTP3 using DAX Timefunctions

 

[neobaron]

Thanks for answering, but it didn´t work for me.

I´ve add it DAX All
Prueba:=If(AVERAGE(Combo[Price])=0,CALCULATE(SUM(Combo[Unit Price]),LASTNONBLANK('Date'[Date],COUNTROWS(RELATEDTABLE('Combo'))),ALL('Date')), AVERAGE([Unit Price]))

But still have same problem... i don´t know what can i do...

 

[ImkeF]

Check that your row- & column headers/Labels come from your DimDate-Table and not from your FactTable.

 

[billszysz]

Hi neobaron,

You can use Power Query to your original data, transform them in it and add result to your data model. This will be simpler than using PP to calculate this.

Regards

 

[neobaron]

@ImkeF :i´ve tried it, But i couldn´t, maybe i think i have a wrong data model or something like this because the DAX make sense for me.

@billszysz Sorry can you give me more details about your idea? i a newie with PowerQuery so i don´t know what you mean.

My list have +100 products, is not like one product price is more like 100 times photo table. it could be because of that?

 

[ImkeF]

I have no further idea re the PP solution.

Re Power Query:
Assuming that your “Combo”-table has at least these columns: date, UnitPrice & ProductID you’d proceed as follows:

Push combo to PQ as a simple query (call it Combo as well) (Power Query – from Table)

In a second step, push your Date-table there as well – then join it with “Combo” on “date”. Expand ProductIDs and UnitPrice and pivot on ProductID, taking UnitPrice into the ValueSection.

Then mark all your ProductID-columns and under “Transform” choose “Fill” (could be named slightly different, am using localized version) and then choose downward direction. This will produce a the desired price for every line of you date-table.

Then you just unpivot your ProductID columns in order to get back the desired format for your PP datamodel and load it there.