difference of distinct id of previous period from current period

[Aj24]

Hi everyone!

I'm looking to get the difference for the distinct count of ID’s of the current period from the previous period. 

table name 'test'

ID	Name	Question	Rating	Month	initial date	last update month	monthly buckets	previous count for last period (jan 2021-mar 2021 current period)
A-yes	A	yes	Amber	1/10/2020	1/10/2020	1/01/2021	Oct 2020-Dec 2020	1
A-yes	A	yes	Amber	1/01/2021	1/01/2021	1/02/2020	Jan 2021-mar 2021
A-yes	A	yes	Amber	1/02/2021	1/01/2021	1/02/2020	Jan 2021-mar 2021
B-possible	B	possible	Amber	1/10/2020	1/01/2020	1/02/2020	Oct 2020-Dec 2020
B-possible	B	possible	Amber	1/02/2021	1/01/2021	1/05/2021	Jan 2020-mar 2020
B-possible	B	possible	Amber	1/05/2021	1/01/2021	1/05/2021	Apr 2021-Jun 2021
C-not	C	not	red	1/03/2020	1/03/2020	1/04/2020	Jan 2020-mar 2020
C-not	C	not	red	1/04/2020	1/03/2020	1/05/2020	Apr 2020-Jun 2020
C-not	C	not	Amber	1/05/2020	1/05/2020	1/07/2020	Apr 2020-Jun 2020
C-not	C	not	Amber	1/07/2020	1/05/2020	1/07/2020	Jul 2020-Sep 2020

So the scorecard visual count result would be: 1 
desired result: amber for (jan 2021-mar 2021 current period): 1

my custom quarters is split into this formula: custom Q&Y = IF('Test'[Month] = 1 && Test[Year] = 2020, "Jan-Mar 2020",

IF('Test'[Month] = 2 && Test[Year] = 2020, "Jan-Mar 2020",

IF('Test'[Month] = 3 && Test[Year] = 2020, "Jan-Mar 2020",

IF('Test'[Month] = 4 && Test[Year] = 2020, "Apr-Jun 2020")))) Etc

note: the months are broken into; Jan-Mar, Apr-Jun, Jul-Sep, Oct-Dec.

i've been using this formula but it didn't work: 

• #current ID period = DISTINCTCOUNT(Test[ID])
• #Previous ID period = Calculate(distinctcaoutn(test[ID]), Previousquarter(tarp[custom Q&Y])
• #Period Difference of ID = VAR  _CurrentPeriodCount = #current ID period   VAR  _PrevPeriodIDCount = #Previous ID period     VAR   _Result = _CurrentPeriodCount  - _PrevPeriodIDCount          Return _Result

Many thanks in advance for sharing your expert help!

[v-easonf-msft]

Hi,  @Aj24 

Not very clear.It is difficult for me to understand your needs if you only provide unusable formulas.

Can you share expected result of  #current ID period,#Previous ID period ,#Period Difference of ID  in excel?

Based on the information you have provided so far, I suggest to try the following formula to create a calendar table first：

Calendar = CALENDARAUTO()

custom Q&Y = SWITCH(Calendar[Date].[QuarterNo],1,"Jan-Mar",2,"Apr-Jun",3,"Jul-Sep",4,"Oct-Dec")&"  "&Calendar[Date].[Year]

RanK Q&Y = RANKX('Calendar',Calendar[Date].[Year]*10+Calendar[Date].[QuarterNo],,ASC,Dense)

Best Regards,
Community Support Team _ Eason