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!
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!