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

Microsoft is giving away 50,000 FREE Microsoft Certification exam vouchers. Get Fabric certified for FREE! Learn more

Reply
Anonymous
Not applicable

Count of New Instances of a Column

Hello - 

I have a data set that includes scopes of work and the activities associated with each scope.  Each week, new scopes are added.  I would like to be able to get a count of these scopes, week over week.  I'm able to get a count of new scopes this week, but I'm having difficulty getting them for each week.
This is how I'm getting the count this week:

Week on Week SOW Change =
VAR _valThisWeek = P6Data[SOW]
VAR _valLastWeek = P6Data[Last Week SOW]
RETURN
IF(_valThisWeek = _valLastWeek, 0, 1)

Last Week SOW = LOOKUPVALUE(P6Data[SOW], P6Data[Concatenate Reference 1], P6Data[Concatenate Reference 2])
Concatenate Reference 1 = CONCATENATE(P6Data[Concatenate 1],P6Data[Work Week Number])
Concatenate Reference 2 = CONCATENATE(P6Data[Concatenate 1], P6Data[Concatenate Last Week])
Concatenate 1 = CONCATENATE(P6Data[Concatenate Activity ID and Activity Name], P6Data[CX Step])
Concatenate Last Week = P6Data[This Week Number] - 1

I feel like there is an easier way.  For each week, I would like a count of unique SOW that didn't exist in the prior week.  
 
Thanks!


1 REPLY 1
v-jingzhang
Community Support
Community Support

Hi @Anonymous 

 

Your scenario is similar to the New and Returning Customers pattern: New and returning customers – DAX Patterns. You could refer to its solutions to get some ideas.

 

Additionally, if you could share some sample data (remove sensitive information), we could try to find some simpler solutions. And how do you define a new scope? A scope that appears the first time or a scope that just doesn't exist in previous week?

 

Regards,
Community Support Team _ Jing
If this post helps, please Accept it as the solution to help other members find it.

Helpful resources

Announcements
March PBI video - carousel

Power BI Monthly Update - March 2025

Check out the March 2025 Power BI update to learn about new features.

March2025 Carousel

Fabric Community Update - March 2025

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

Top Solution Authors