Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
leahschneider
Helper III
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/22Hat 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
ppm1
Solution Sage
Solution Sage

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], ", " )

ppm1_0-1672281734629.png

 

Pat

Microsoft Employee

View solution in original post

5 REPLIES 5
ppm1
Solution Sage
Solution Sage

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], ", " )

ppm1_0-1672281734629.png

 

Pat

Microsoft Employee

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.

Arul
Super User
Super User

@leahschneider ,

try this formula in measure,

 

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

 

Arul_0-1672256869953.png

Thanks,

Arul





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!


LinkedIn


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.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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

Top Solution Authors