March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.
Register NowGet certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now
Hello to all the experts,
I have a database, that contains Warehouses inventory.
The columns are: Item ID / Warehouse name / qty. in WH / Last transaction date.
My goal is:
Get the most recent transaction date, for items that has total quantity of 0, in all WH's, in which they are contained.
In this example: items 101, 104 and 106.
(Asume that the table is sorted by Item ID and Last Trans. date)
Item ID | WH | Qty. in WH | Date Last Trans. | Item ID | WH | Qty. in WH | Date Last Trans. | |
101 | A | 0 | 02/01/23 | Results: | 101 | C | 0 | 24/02/23 |
101 | B | 0 | 17/01/23 | 104 | B | 0 | 05/01/23 | |
101 | C | 0 | 24/02/23 | 106 | D | 0 | 30/03/23 | |
102 | A | 126 | 20/02/23 | |||||
103 | A | 14 | 12/03/23 | |||||
104 | B | 0 | 05/01/23 | |||||
105 | B | 0 | 15/01/23 | |||||
105 | C | 41 | 16/01/23 | |||||
106 | A | 0 | 20/03/23 | |||||
106 | C | 0 | 25/03/23 | |||||
106 | D | 0 | 30/03/23 | |||||
107 | A | 1 | 31/03/23 | |||||
107 | C | 0 | 01/04/23 | |||||
107 | D | 0 | 02/02/23 | |||||
108 | C | 18 | 03/01/23 |
Solved! Go to Solution.
Hi, Please check the below picture and the attached pbix file.
It is for creating a new table.
Expected table =
VAR _expectedresulttable =
CALCULATETABLE (
Data,
FILTER (
VALUES ( Data[Item ID] ),
CALCULATE ( ABS ( SUM ( Data[Qty. in WH] ) ) ) = 0
)
)
VAR _groupbylatestdate =
GROUPBY (
_expectedresulttable,
Data[Item ID],
"@latestdate", MAXX ( CURRENTGROUP (), Data[Date Last Trans.] )
)
RETURN
CALCULATETABLE (
Data,
TREATAS ( _groupbylatestdate, Data[Item ID], Data[Date Last Trans.] )
)
If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.
Thanks to the great efforts by MS engineers to simplify syntax of DAX! Most beginners are SUCCESSFULLY MISLED to think that they could easily master DAX; but it turns out that the intricacy of the most frequently used RANKX() is still way beyond their comprehension! |
DAX is simple, but NOT EASY! |
I found some mistakes in this solution, but thanks anyway for your replay 👍
Thanks to the great efforts by MS engineers to simplify syntax of DAX! Most beginners are SUCCESSFULLY MISLED to think that they could easily master DAX; but it turns out that the intricacy of the most frequently used RANKX() is still way beyond their comprehension! |
DAX is simple, but NOT EASY! |
Thanks a lot, it did the work!
Hi, Please check the below picture and the attached pbix file.
It is for creating a new table.
Expected table =
VAR _expectedresulttable =
CALCULATETABLE (
Data,
FILTER (
VALUES ( Data[Item ID] ),
CALCULATE ( ABS ( SUM ( Data[Qty. in WH] ) ) ) = 0
)
)
VAR _groupbylatestdate =
GROUPBY (
_expectedresulttable,
Data[Item ID],
"@latestdate", MAXX ( CURRENTGROUP (), Data[Date Last Trans.] )
)
RETURN
CALCULATETABLE (
Data,
TREATAS ( _groupbylatestdate, Data[Item ID], Data[Date Last Trans.] )
)
If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.
Thanks a lot, it did the work!
Based on your description, I created data to reproduce your scenario. The pbix file is attached in the end.
Get the most recent transaction date.pbix
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.
Check out the November 2024 Power BI update to learn about new features.
User | Count |
---|---|
118 | |
96 | |
82 | |
69 | |
61 |
User | Count |
---|---|
138 | |
115 | |
115 | |
99 | |
98 |