Calculate the first release dates of all products in a database so they can be align and first w

[rar4010]

Hi All -

I think I got this sorted out after staring at the blog post referenced here and much trial/error.

I created calculated columns for the Monthly Depreciation and the life of the asset expressed in months. Then I changed my calculations for each of the years' depreciation amounts to be a SUMX of Monthly Depreciation * the correct number of months for that year (based on what month it was acquired) and left in the USERELATIONSHIP statement which is the key to getting the annual totals. I can now spread the depreciation over the years by referencing Year from the Date table.

Thanks for all the suggestions!

[MattMarshall]

Hi all

I managed to build a model from other posts that enables me to compare the sales of products launched at different release dates by aligning the first week sales etc. However, I have to manually enter the release dates into a separate table. If I try and do it dynamically, using Firstdate function it always seems to default to the first date for any transaction in the database.

http://www.powerpivotpro.com/2014/06/compare-product-performance-after-launch/

Any my thoughts would be gratefully accepted.

Thanks

Matt

[AlexChen]

Hi,

You don’t need to enter first release date manually, you can use a formula to create a table. See my sample below.

I assume you have a table called “sales” like below.

Create a table “sales2” to get first release day of every product.

sales2 = SUMMARIZECOLUMNS(sales[product], "first day", min(sales[release date]), "sale amount", sumx(filter(sales, sales[release date] = min(sales[release date])), sales[sales amount]))

This is the result:

Best Regards

Alex

[mrqs]

Hi

I have a similar challenge (and I'm still quite new to PowerBI)

Is there a way to do the same in a Direct Query mode and without using additional table?

The output, I would be satisfied with is like this (using the data from the initial question):

A   B     C                   D                   E

Nr Item Sale date     Sale amount  First sale

1   Car   1/17/2016   10000             1/17/2016

2   Car   2/5/2016     10000             1/17/2016

.

.

6   Bike  7/2/2016     10000             7/2/2016

7   Bike  8/9/2016     10000             7/2/2016

.

.

etc

as an equivalent of this formula in Excel:

 (in column E) =INDEX(C:C,MATCH(B1,B:B,0))

To be more precise: my table contains products and their BOM components, that are short in qty and also their requirement date and planned delivery. Since some parts appear multiple times, I need the earliest demand date assigned to each line with given component.

My source data in on SQL server and as said - I'm using direct query to get the data.

Thanks in advance!

Marek