topic Re: How to get list of new items per month in DAX Commands and Tips

How to get list of new items per month

leahschneider — Wed, 28 Dec 2022 20:44:22 GMT

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?

Re: How to get list of new items per month

Arul — Wed, 28 Dec 2022 19:48:49 GMT

leahschneider ,

try this formula in measure,

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

Thanks,

Arul

Re: How to get list of new items per month

leahschneider — Wed, 28 Dec 2022 20:25:25 GMT

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.

Re: How to get list of new items per month

ppm1 — Thu, 29 Dec 2022 02:43:44 GMT

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

Re: How to get list of new items per month

leahschneider — Thu, 29 Dec 2022 18:01:06 GMT

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.

Re: How to get list of new items per month

Anonymous — Thu, 07 Mar 2024 15:02:37 GMT

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?