Return null in a specific condition

[William_Moreno]

Hello people,

I need help from the experts powerbi.

I have the following spreadsheet, formed by the fields: ID_PRODUCT, SALES_MONTH, QTY.

From this, I want to create two new columns:

• FIRST SALE - column that refer to the month of first sale that occurred for a given product;
• QTY (REVIEWED) - it is expected in the cases where the month (SALES_MONTH) is before that FIRST SALE result value equals null not zero.

ID PRODUCT	SALES_MONTH	QTY	FIRST SALE	QTY (REVIEWED)
100500100	01/01/2020	0	01/05/2020	null
100500100	01/02/2020	0	01/05/2020	null
100500100	01/03/2020	0	01/05/2020	null
100500100	01/04/2020	0	01/05/2020	null
100500100	01/05/2020	1000	01/05/2020	1000
100500100	01/06/2020	1500	01/05/2020	1500
100500100	01/07/2020	2000	01/05/2020	2000
100500100	01/08/2020	3000	01/05/2020	3000

Is it possible?

Thanks in advance!

[Greg_Deckler · Accepted solution]

@William_Moreno - First will be:

First = 
    MINX(FILTER('Table (27)',[ID PRODUCT]=EARLIER([ID PRODUCT]) && [SALES_MONTH]>=EARLIER([SALES_MONTH]) && [QTY]<>0),[SALES_MONTH])

 Reviewed

Reviewed = IF([SALES_MONTH]<[First] && [QTY]=0,BLANK(),[QTY])

PBIX attached.

[William_Moreno]

Greg, thank you for your post.

In my pbi archive I've changed just one signal of the function, like this:

First = 
    MINX(FILTER('Table (27)',[ID PRODUCT]=EARLIER([ID PRODUCT]) && [SALES_MONTH]<=EARLIER([SALES_MONTH]) && [QTY]<>0),[SALES_MONTH])

[amitchandak]

@William_Moreno ,

First Sales = minx(filter(Retail, Retail[SALES_MONTH]=EARLIER(Retail[PRODUCT])),Retail[SALES_MONTH])

I did not second, How can we have something before first sales?

[William_Moreno]

Yes, you're right but, when this field is zero not null, the average is changed. Imagine if we were talking about a new product, month before that first sale it can not influence the average, Do you agree? 

Anyway thank you for your post.