Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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.
Solved! Go to Solution.
@schoden , refer to page two of the attached file after Signature, I created a flag
@schoden , refer to page two of the attached file after Signature, I created a flag
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.
@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.
@schoden , You can create year on date or using related
year = related(Date[Year])
Or
Year = Year([Date])
@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.
User | Count |
---|---|
97 | |
85 | |
77 | |
66 | |
62 |
User | Count |
---|---|
110 | |
96 | |
95 | |
64 | |
57 |