cancel
Showing results for
Did you mean:

Fabric is Generally Available. Browse Fabric Presentations. Work towards your Fabric certification with the Cloud Skills Challenge.

Helper III

## How to get list of new items per month

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?

1 ACCEPTED SOLUTION
Solution Sage

``````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

Microsoft Employee
4 REPLIES 4
Solution Sage

``````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

Microsoft Employee
Helper III

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.

Super User

try this formula in measure,

``````Concatenate =
CALCULATE (
CONCATENATEX ( DISTINCT ( 'Samp Table1'[Item ] ), 'Samp Table1'[Item ], "," ),
ALLEXCEPT ( 'Samp Table1', 'Samp Table1'[Date ] )
)``````

Thanks,

Arul

Proud to be a Super User!

Helper III

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.