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.
I have a table where there is a client ID (1234,2345,3456, etc.). Each client has multiple dates of service. I want to filter out the top two dates of service for each and then calculate the datediff between the two dates that remain. The client_id and service_date are the column names. I've tried indexing, a calculated column, and pulling out my hair. I'm down to my last strand. Please help. Some clients have 5 dates of service while others only have 2. Some have just one and those need to be filtered out completely.
The top two would be the oldest two.
Solved! Go to Solution.
Just to give help you a bit more, you can then get the first and second date using the following measure
First Date = MAXX(FILTER('Table1', [Date Rank] = 1), 'Table1'[Date].[Date])
Second Date = MAXX(FILTER('Table1', [Date Rank] = 2), 'Table1'[Date].[Date])
To get the difference you would then simply use the following and put it against UserId in a table
Date Diff = DATEDIFF([FirstDate], [Second Date], DAY)
Hopefully this helps get you close enough!
Any updates on this? Were are you able to solve this problem?
Hi,
Please share some data and show the expected result.
Could you use the rankx function and filter on <= 2?
I was trying to figure that out, because the filtering I though worked, didn't. How would I rankx for each client's dates of service and not a rankx of all dates of service?
Assuming you have a table with two columns, UserId and Date, the following calculated column ranks each date by UserId
Date Rank = RANKX(CALCULATETABLE('Table1', ALLEXCEPT(Table1, Table1[UserId])), 'Table1'[Date], , ASC)
Just to give help you a bit more, you can then get the first and second date using the following measure
First Date = MAXX(FILTER('Table1', [Date Rank] = 1), 'Table1'[Date].[Date])
Second Date = MAXX(FILTER('Table1', [Date Rank] = 2), 'Table1'[Date].[Date])
To get the difference you would then simply use the following and put it against UserId in a table
Date Diff = DATEDIFF([FirstDate], [Second Date], DAY)
Hopefully this helps get you close enough!
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 |
---|---|
110 | |
99 | |
80 | |
64 | |
57 |
User | Count |
---|---|
145 | |
110 | |
91 | |
84 | |
66 |