Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
Hi all!
I am trying to determine the order of events based on dates. I have an identifier column that says if it’s the first event or last event. But I am clueless on how to determine anything in between. Would you have any suggestions for me?
Sample table I’m working with.
ClientID | Price | Date | FirstSale | LastSale |
1 | 50 | 01.01.2023 | 01.01.2023 | 12.05.2023 |
1 | 30 | 24.04.2023 | 01.01.2023 | 12.05.2023 |
1 | 55 | 27.04.2023 | 01.01.2023 | 12.05.2023 |
1 | 80 | 12.05.2023 | 01.01.2023 | 12.05.2023 |
2 | 15 | 27.11.2022 | 27.11.2022 | 01.04.2023 |
2 | 40 | 05.02.2023 | 27.11.2022 | 01.04.2023 |
2 | 50 | 10.03.2023 | 27.11.2022 | 01.04.2023 |
2 | 35 | 01.04.2023 | 27.11.2022 | 01.04.2023 |
I am expecting to write a measure or a new column that would identify whether a certain row contain data for 2nd, 3rd, etc sale.
Would you please be able to help me with an idea that could solve this?
Solved! Go to Solution.
You could do it either as a column or as a measure
Rank Column =
RANK(, ALL( 'Table'[ClientID], 'Table'[Date] ),
ORDERBY( 'Table'[Date], ASC),
PARTITIONBY('Table'[ClientID]) )
Rank Measure =
RANK(, ALLSELECTED( 'Table' ),
ORDERBY( 'Table'[Date], ASC),
PARTITIONBY('Table'[ClientID]) )
You could do it either as a column or as a measure
Rank Column =
RANK(, ALL( 'Table'[ClientID], 'Table'[Date] ),
ORDERBY( 'Table'[Date], ASC),
PARTITIONBY('Table'[ClientID]) )
Rank Measure =
RANK(, ALLSELECTED( 'Table' ),
ORDERBY( 'Table'[Date], ASC),
PARTITIONBY('Table'[ClientID]) )
User | Count |
---|---|
107 | |
89 | |
81 | |
76 | |
73 |
User | Count |
---|---|
112 | |
104 | |
96 | |
74 | |
66 |