Check your eligibility for this 50% exam voucher offer and join us for free live learning sessions to get prepared for Exam DP-700.
Get StartedDon't miss out! 2025 Microsoft Fabric Community Conference, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount. Prices go up February 11th. Register now.
I can't seem to find an answer that works for this. Basically I have a table that looks like this:
Date | Item | Order |
6/1/22 | Bike | 50 |
6/1/22 | Tire | 1 |
6/1/22 | Bike | 25 |
6/1/22 | Shirt | 40 |
5/1/22 | Shirt | 10 |
5/1/22 | Shirt | 1 |
4/1/22 | Hat | 15 |
I'd like to see something that shows the new items per month concatenated like below:
Date | New Items |
6/1/22 | Bike, Tire |
5/1/22 | Shirt |
4/1/22 | Hat |
Can someone help with this?
Solved! Go to Solution.
Please try this measure expression.
NewItems =
VAR thisdate =
MAX ( Orders[Date] )
VAR newitemstable =
FILTER (
DISTINCT ( Orders[Item] ),
ISBLANK (
CALCULATE (
COUNTROWS ( Orders ),
ALL ( Orders[Date] ),
Orders[Date] < thisdate
)
)
)
RETURN
CONCATENATEX ( newitemstable, Orders[Item], ", " )
Pat
Please try this measure expression.
NewItems =
VAR thisdate =
MAX ( Orders[Date] )
VAR newitemstable =
FILTER (
DISTINCT ( Orders[Item] ),
ISBLANK (
CALCULATE (
COUNTROWS ( Orders ),
ALL ( Orders[Date] ),
Orders[Date] < thisdate
)
)
)
RETURN
CONCATENATEX ( newitemstable, Orders[Item], ", " )
Pat
This helped me resolve one of the things I need, thanks! Not the exact similar case as I don't need the concatenation, I just need the count of new items for the current month.
However, I am having troubles doing the flipside of this -- ie, I want to get the number of items that were existing in the previous month but not in the current month.
Would you happen to know the trick?
This worked but with FIRSTDATE instead of MAX. Max created incorrect rollups.
I'd like to add for future reference that I couldn't get this to work in my actual table which is a big and complicated fact table so I created a small table with summarize to get just the fields I wanted.
try this formula in measure,
Concatenate =
CALCULATE (
CONCATENATEX ( DISTINCT ( 'Samp Table1'[Item ] ), 'Samp Table1'[Item ], "," ),
ALLEXCEPT ( 'Samp Table1', 'Samp Table1'[Date ] )
)
Thanks,
Arul
That's incorrect even in your picture. I only want new items for that month, not a distinct list of all items. Your solution has Shirt in June and in May and I only want to see it in May.
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Prices go up Feb. 11th.
Check out the January 2025 Power BI update to learn about new features in Reporting, Modeling, and Data Connectivity.
User | Count |
---|---|
21 | |
15 | |
14 | |
11 | |
7 |
User | Count |
---|---|
26 | |
24 | |
12 | |
11 | |
10 |