Forum Discussion
Helper IIReturn null in a specific condition
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!
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.
4 Replies
- amitchandak
Super User
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_MorenoYes, 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.
- Greg_Deckler
Community Champion
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])
