whenCount of ID's removed or changed from one date to another in same table

[Hayleysea]

Hi There,

I have data over many weeks with different ID's and periods in one table. I need to see for a particular period, how many ID's have changes from one specific date to another (I would use a filter to filter on the Month's I want to compare). The scenarios I need to count for are:

1. ID's that have been removed

2. ID's that have moved periods

Example below:

ID	Date	Period
A	May	Q1
B	May	Q1
C	May	Q1
A	June	Q1
B	June	Q3
C	June	Q1
B	July	Q3
C	July	Q1

Results should be as per below when comparing May with July:

Count of ID's removed = 1 (A)

Count of ID's changed period = 1 (B moved from Q1 to Q3)

Any help greatly appreciated 🙂

[Hayleysea]

Hi All, I have managed to solve problem 1 (ID's that have been removed) but am still struggling with ID's that have moved quarters. I've tried using above suggestions but still can't get it to work. Any more ideas? thanks

[amitchandak]

@Hayleysea , I am hoping you have a date. I think this can be done using time intelligence. Like what blank this month vs last month.

I have a blog on the same. Their customer ID is like you ID

https://community.powerbi.com/t5/Community-Blog/Customer-Retention-Part-1-Month-on-Month-Retention/b...

[Greg_Deckler]

@Hayleysea Do you have an Index or actual Date column in your source data? Is that source data posted below? If not, can you post sample data? Without an Index or Date the calculation becomes kind of ugly trying to figure out previous.

[Hayleysea]

I do have a proper date column that can be used and a week rank column.

[Greg_Deckler]

@Hayleysea OK, slight error in previous reply, PBIX attached below sig. GROUPBY, not SUMMARIZE!! You want Page 3, Table (3)

Measure 3a = 
  VAR __Current = MAX([Date])
  VAR __PreviousDate = MIN([Date])
  VAR __TableCurrent = FILTER('Table (3)',[Date]=__Current)
  VAR __TablePrevious = FILTER('Table (3)',[Date]=__PreviousDate)
  VAR __Table = SELECTCOLUMNS('Table (3)',"ID",[ID],"Period",[Period])
  VAR __Table1 =
    EXCEPT(
      SELECTCOLUMNS(__TablePrevious,"ID",[ID]),
      SELECTCOLUMNS(__TableCurrent,"ID",[ID])
    )
  VAR __Table2 = FILTER(__Table,NOT([ID] IN __Table1))
  VAR __Table3 = DISTINCT(__Table2)
  VAR __Table4 = GROUPBY(__Table3,[ID],"Count",COUNTX(CURRENTGROUP(),[Period])) 
RETURN
  COUNTROWS(FILTER(__Table4,[Count]>1))

@ me if this doesn't work for you. Both are in the file.

[Greg_Deckler]

@Hayleysea  Whew! OK, the pattern goes along these lines:

Measure =
  VAR __Current = MAX([Date])
  VAR __PreviousDate = MIN([Date])
  VAR __TableCurrent = FILTER('Table',[Date]=__Current)
  VAR __TablePrevious = FILTER('Table',[Date]=__Previous)
RETURN
  COUNTROWS(
    EXCEPT(
      SELECTCOLUMNS(__TablePrevious,"ID",[ID]),
      SELECTCOLUMNS(__TableCurrent,"ID",[ID])
    )
  )

Something like that. Actual source data would be great to be more specific. I hesitate attempting the change one without proper sample data posted as text, a lot of variables to consider.

Maybe:

Measure =
  VAR __Current = MAX([Date])
  VAR __PreviousDate = MIN([Date])
  VAR __TableCurrent = FILTER('Table',[Date]=__Current)
  VAR __TablePrevious = FILTER('Table',[Date]=__Previous)
  VAR __Table = SELECTCOLUMNS('Table',"ID",[ID],"Period",[Period])
  VAR __Table1 =
    EXCEPT(
      SELECTCOLUMNS(__TablePrevious,"ID",[ID]),
      SELECTCOLUMNS(__TableCurrent,"ID",[ID])
    )
  VAR __Table2 = FILTER(__Table,NOT([ID] IN __Table1))
  VAR __Table3 = DISTINCT(__Table2)
  VAR __Table4 = SUMMARIZE(__Table3,[ID],"Count",COUNT([Period])) 
RETURN
  COUNTROWS(FILTER(__Table4,[Count]>1))

So hard to test without data, might be missing something. Syntax errors.