Forum Discussion
Helper INeed help with array formula equivalent in power bi using IF and MAX
Hi is there an equivalent for array formula in power bi for the below example?
My original database has only "EMPLOYEE", "SALES_DATE" & "SALES VALUE". I would like to calculate the highest sales of each employee, based on their latest sales.
What I normally do in Excel would be to first determine the latest sales i.e. Column D "LATEST_SALES_DATE" by using the array formula {=MAX(IF(A2=A:A,B:B))}
Next, I would use determine the highest sales value of the latest date i.e. Column E "SALES_VALUE_FROM_LATEST_SALES" by using the array formula {=MAX(IF(A2&D2=A:A&D:D,C:C))}
Reason I would need the second array formula "{=MAX(IF(A2&D2=A:A&D:D,C:C))}" is because it is possible that the employee might have multiple sales on the same day e.g. Employee A made 2 sales on 6 Jun 20; $40 & $50. And I am only interested in the $50 sale.
Would there be anyway to do this in power bi? If so, would it be also possible to have 1 array formula to get the SALES_VALUE_FROM_LATEST_SALES without having to do the intermediate step of determining LATEST_SALES_DATE first?
Thanks!
| A | B | C | D | E | |
1 | EMPLOYEE | SALES_DATE | SALES_VALUE | LATEST_SALES_DATE | SALES_VALUE_FROM_LATEST_SALES |
| 2 | A | 1/1/20 | $100.00 | 6/6/20 | $50.00 |
| 3 | A | 6/6/20 | $50.00 | 6/6/20 | $50.00 |
| 4 | A | 6/6/20 | $40.00 | 6/6/20 | $50.00 |
| 5 | B | 3/2/20 | $200.00 | 8/8/20 | $30.00 |
| 6 | B | 8/8/20 | $30.00 | 8/8/20 | $30.00 |
| 7 | B | 5/5/20 | $50.00 | 8/8/20 | $30.00 |
| 8 | C | 2/3/20 | $150.00 | 25/12/20 | $80.00 |
| 9 | C | 8/9/20 | $250.00 | 25/12/20 | $80.00 |
| 10 | C | 25/12/20 | $80.00 | 25/12/20 | $80.00 |
Hi, matthewtjy
According to your description and sample data, I can understand clearly what you want to get, you want to get the sales value of the latest date based on the [Employee], right? If so, I guess that the sample picture you posted is incorrect in someplace, you should check:
I think you can achive this using calculated column in DAX, you can try this:
SALES_VALUE_FROM_LATEST_SALES = var _latestdate=MAXX(FILTER(ALL('Table'),[EMPLOYEE]=EARLIER('Table'[EMPLOYEE])),[SALES_DATE]) return CALCULATE(SUM('Table'[SALES_VALUE]),FILTER(ALL('Table'),[SALES_DATE]=_latestdate&&[EMPLOYEE]=EARLIER('Table'[EMPLOYEE])))And you can get what you want, like this:
If you also want to get the latest date based on the [Employee], you can try this DAX:
LATEST_SALES_DATE = MAXX(FILTER(ALL('Table'),[EMPLOYEE]=EARLIER('Table'[EMPLOYEE])),[SALES_DATE])You can download my test pbix file here
Thank you very much!
Best Regards,
Community Support Team _Robert Qin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
3 Replies
- amitchandak
Super User
matthewtjy , Try a measure like
Measure =
VAR __id = MAX ('Table'[EMPLOYEE] )
VAR __date = CALCULATE ( MAX('Table'[SALES_DATE] ), ALLSELECTED ('Table' ), 'Table'[EMPLOYEE] = __id )
CALCULATE ( Sum ('Table'[SALES_VALUE] ), VALUES ('Table'[EMPLOYEE] ),'Table'[EMPLOYEE] = __id,'Table'[SALES_DATE] = __date )or a measure like
calculate(lastnonblankvalue('Table'[SALES_DATE] ,Sum ('Table'[SALES_VALUE])), filter(ALLSELECTED ('Table' ) , 'Table'[EMPLOYEE] =max('Table'[EMPLOYEE])))
matthewtjySorry, I'm not quite sure what do you mean. I was wondering if it was possible to create a new column using the array formula equivalent, and then create a new table and delete the duplicate rows?
- v-robertq-msft
Community Support
Hi, matthewtjy
According to your description and sample data, I can understand clearly what you want to get, you want to get the sales value of the latest date based on the [Employee], right? If so, I guess that the sample picture you posted is incorrect in someplace, you should check:
I think you can achive this using calculated column in DAX, you can try this:
SALES_VALUE_FROM_LATEST_SALES = var _latestdate=MAXX(FILTER(ALL('Table'),[EMPLOYEE]=EARLIER('Table'[EMPLOYEE])),[SALES_DATE]) return CALCULATE(SUM('Table'[SALES_VALUE]),FILTER(ALL('Table'),[SALES_DATE]=_latestdate&&[EMPLOYEE]=EARLIER('Table'[EMPLOYEE])))And you can get what you want, like this:
If you also want to get the latest date based on the [Employee], you can try this DAX:
LATEST_SALES_DATE = MAXX(FILTER(ALL('Table'),[EMPLOYEE]=EARLIER('Table'[EMPLOYEE])),[SALES_DATE])You can download my test pbix file here
Thank you very much!
Best Regards,
Community Support Team _Robert Qin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
