How to get list of new items per month

[leahschneider]

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?

[ppm1 · Accepted 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

[raellaylo]

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?

[leahschneider]

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.

[Arul]

@leahschneider ,

try this formula in measure,

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

Thanks,

Arul

[leahschneider]

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.