Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
111 | |
97 | |
80 | |
69 | |
59 |
User | Count |
---|---|
150 | |
119 | |
104 | |
87 | |
67 |