Values for previous and after year 2014

[brijendraydv]

Hi Experts,

I have high hopes from you to solve this tricky powerbi automation requirement.
I have attached the screenshot of how i want the output at the end.
I want to keep year 2014 as reference .
So if any supplier with some part number in some plant as shown in image has no 2014 rate then take the immediate previous year rate as 2014 rate like PN- 222163-1 has only price in 2011 so it becomes price for 2014.
Now after getting the year14 price we go for 2015 which is not there again so 2014 price becomes 2015 price and likewise for 2016 and 2017.
Is it possible to use any calendar date function like with if condition using previous year calculation?

Once done i need some analytics on top of it like after this i need to know which supplier in same plant is having different prices.

Pls recommend your expertise.

Thanks,

 

[NARAYANK991]

Hi ,

I am not clear on what part of your requirement has to do with Power BI.

Can you upload your Excel workbook , so that your data layout is available ?

Narayan

 

[brijendraydv]

Hi Narayan,

Thanks,PFA the sample file with output as explanation.
Data size is in millions so would like to use powerquery/PBI and ontop of it want to utilize PBI time intelligence visuals for analytics.

 

[brijendraydv]

Any updates or its too complex?

 

[brijendraydv]

Any expert can reply pls

 

[Chihiro]

Power BI isn't part of Excel but is separate entity. Do you want to do this in Power BI or in Excel using PowerQuery/PowerPivot?

Also, I'm not entirely certain what you are trying to accomplish here.
I need bit more sample and explanation.

 

[brijendraydv]

Hi,

I have 2 million records and want to do time intelligence analytics once the data is in proper shape.
I have already attached the sample file(chandoo.xlsx)-with explanation in the output tab.
pls let me know if anything else you need.

BR
Brij

 

[Chihiro]

You didn't answer any of my question/request.

1. Is it going to be in Power BI or in Excel? Two separate program you are talking about. While underlying structure is same (DAX, M etc), there are some differences which does not translate well from one to the other.

2. Your explanation was unclear and there isn't enough data sample for me to test all scenario. That's why I requested additional data to what you already uploaded.