Hi everyboy,
I have a problem. Now it is a big problem. I went through all threads about DAX & PowerBI and I cannot find a correct solution.
What is the point?
I have a dataset to analyze. It includes:
- "Article" table where different articles (items) are stored
- "Warehouse Documents" table where each order has its own counterpart; each order can have more than one article on it of course
- "Position of Warehouse Documents" table that has connection to "Warehouse Documents" and "Article" so I can find for instance on which orders one single article has been sold and etc
So, I have already made a different measures, calculations and so on and it works quite fine.
But, I am stucked with one info: I would like to add to my dashboard - the DATE of the LAST ORDER for ARTICLE
Let's get the example:
"ARTICLE" table:
| Product_Name | TradeIndex | UniquePrice | Category | ID_Article |
| Item_1 | 12345671 | 12,45€ | A1 | 1 |
| Item_2 | 12345672 | 23,56€ | A2 | 2 |
| Item_3 | 12345673 | 10,00€ | B1 | 3 |
| Item_4 | 12345674 | 21,50€ | B1 | 4 |
| Item_5 | 12345675 | 8,56€ | B1 | 5 |
| Item_6 | 12345676 | 100,00€ | A2 | 6 |
"WAREHOUSE DOCUMENTS" table:
| | Date | ID_WAREHOUSE_DOCUMENTS |
| Order_1 | 12.04.2010 | 1 |
| Order_2 | 18.07.2011 | 2 |
| Order_3 | 21.03.2015 | 3 |
| Order_4 | 22.05.2019 | 4 |
| Order_5 | 18.03.2020 | 5 |
"Position Warehouse Documents" table:
| ID_Position_Warehouse_Documents | ID_Warehouse_Documents | Date | ID_Article | Quantity | Price |
| 1 | 1 | 12.04.2010 | 2 | 10 | 100€ |
| 2 | 1 | 12.04.2010 | 1 | 20 | 199€ |
| 3 | 2 | 18.07.2011 | 2 | 14 | 140€ |
| 4 | 2 | 18.07.2011 | 3 | 10 | 150€ |
| 5 | 2 | 18.07.2011 | 1 | 1 | 200€ |
| 6 | 3 | 21.03.2015 | 2 | 2 | 50€ |
| 7 | 3 | 21.03.2015 | 4 | 10 | 20€ |
| 8 | 3 | 21.03.2015 | 3 | 5 | 10€ |
As you see ARTICLE "Item_2" has been sold three times and I would like to get the DATE of the LAST ORDER so in this case would be : 21.03.2015
For Item_1 it would be 18.07.2011 and for Item_3 = 21.03.2015
Can you tell me how I can calculate it?
I have already tried different functions: CALCULATE, CALCULATETABLE, ADDCOLUMNS and many others but to be honest I don't know which one I should use. Honestly I am lost here ... I hope the example is clear.
If not please let me know. Thanks in advance for any suggestions - it shoulnd't be so tricky 😞
BR,
MrAnalytic
