Continous Year

[schoden]

Hi Community, 

How can I check if a customer year of transaction is continous or not. 

In the table,  the customer A is not continous as 2018 transaction is missed. 

I have calender table and customer table.

Year	Customer	Revenue
2015	A	50
2016	A	100
2017	A	20
2019	A	40
2020	A	200

Thanks.

[amitchandak · Accepted solution]

@schoden , refer to page two of the attached file after Signature, I created a flag

[schoden]

Thank you @amitchandak  So much 

[amitchandak]

Is your base data same as what you shared initially? If not, Can you share sample data and sample output in table format? I need to try on data now.

[schoden]

Hi @amitchandak   I have created a sample data. 

Customer A and B has continous year transaction but not Customer C and D. 

[amitchandak]

@schoden , Create a new column of the year first and try. Earlier will not allow quite a few options

[schoden]

@amitchandak  got the diff column running.  But I am not able to acheive the outcome of my purpose.

Final measure =calculate(distinctcount(v_api_find_Opportunity[Company_RecID]) , filter(v_api_find_Opportunity, v_api_find_Opportunity[Diff]>1))

This measures return YEAR 2012 records only.

[amitchandak]

@schoden , You can create year on date or using related

year = related(Date[Year])

Or

Year = Year([Date])

[schoden]

@amitchandak it still shows red line.

[amitchandak]

@schoden , Have a new column like this in you fact

Diff = [Year] - maxx(filter(Table,[customer] =earlier([customer]) && [Year] <earlier([Year])),[Year])

And then use a measure like

Measure = calculate(distinctcount(Table[Customer]) , filter(Table, Table[Diff]>1))

[schoden]

@amitchandak  I have Year from Calender table . 

If the new column is created in fact table, Year gets red underlined. 

How Can i refer year from calender table in the fact table.