Count of New Instances of a Column

[emilymgoitia]

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!

[v-jingzhang]

Hi @emilymgoitia 

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.