cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

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
5 REPLIES 5
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
Anonymous
Not applicable

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?

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.

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

#### Power BI Monthly Update - August 2024

Check out the August 2024 Power BI update to learn about new features.

#### Fabric Community Update - August 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors