Forum Discussion
Continous Year
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.
schoden , refer to page two of the attached file after Signature, I created a flag
10 Replies
schoden , refer to page two of the attached file after Signature, I created a flag
Thank you amitchandak So much
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))
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.
schoden , You can create year on date or using related
year = related(Date[Year])
Or
Year = Year([Date])
amitchandak it still shows red line.
schoden , Create a new column of the year first and try. Earlier will not allow quite a few options
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.
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.
Hi amitchandak I have created a sample data.
Customer A and B has continous year transaction but not Customer C and D.
