Cumulative counting based only on the earliest date per ID

[chris_k]

Hello,

I am currently working with a data set to produce cumulative counts (which I have working perfectly) however I have a problem with my next step.

The data should all at some point go through "Stage 1" however due to various reasons it doesn't always happen.

My data looks like this:

ID	Stage	Date Entering Stage
ABC1	Stage 1	01/01/2010
ABC2	Stage 2	02/01/2010
ABC3	Stage 1	12/01/2010
ABC1	Stage 2	07/01/2010
ABC1	Stage 3	09/01/2010
ABC2	Rejected	10/01/2010
ABC3	Stage 3	19/01/2010

I have produced cumulative lines based on Stage 1, Stage 2, Stage 3 and Rejected and have them working. What I need is another cumulative line of the earliest date the ID appeared. A first recorded in the system line.  

So for ABC1 this would be 01/01/2010 in Stage 1 as it should be.

But ABC2 doesn't have a Stage 1 so I need to assume the Stage 1 date was the first date in the system (02/01/2010 Stage 2)

My first thought was to add rows for every ID missing Stage 1 using the earliest date found for that ID but I cant figure out how to do it? Or is there a way to produce a cumulative count filtered by only the first appearance of the ID?

DAX or PowerQuery solutions would be fine... Unfortunately there's no way to change the source data.

Thank you,

[v-shex-msft · Accepted solution]

Hi @chris_k,

For your scenario, I have two ideas to deal with this, perhaps you can refer to below formulas.

1. Use min date of whole table as the first date of loss 'stage 1' records.

FirstDate = 
var lookup=LOOKUPVALUE('Sample'[Date Entering Stage],'Sample'[ID],[ID],'Sample'[Stage],"Stage 1")
return
IF(ISBLANK(lookup),FIRSTDATE(ALL('Sample'[Date Entering Stage])),lookup)

2. Use exist min date of current record as the first date.

FirstDate2 = MINX(FILTER(ALL('Sample'),[ID]=EARLIER('Sample'[ID])),[Date Entering Stage])

Result:

Regards,

Xiaoxin Sheng

[chris_k]

Number 2 did exactly as I needed. Thank you.

[CahabaData]

I have a solution that I'm hesitant to post because it is such a kludge - also I come from the database world and think in terms of a sequence of sql queries that would perform this.  At a high level this is what must be done (if one is in a database world).

a. list of unique IDs

b. list of IDs with Stage 1 (only)

c. outer join (b) list to (a) list - - and where there are nulls in the a list in the Stage column - these are the one's you need to deal with

d. insert a value where is null

e. append to original table

I could set this up relatively quickly if I were using database technology, although it is still difficult to explain - however to implement this with the Query Editor in PBI is going to be a bunch of steps - and since there is no double joins allowed one is going to need to create a merge field of ID & Stage in order to accomplish the outer join.....

Hopefully someone better than I at DAX will see this and suggest a more sane solution......