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
I would like to count the number of days between start and finish of a certain process as illustrated in the table below: “Log on” 8.11.18 and “Completed” 8.15.18 = 4 days. The problem is that the activity “Completed” appears in two different rows (the activity is for some reason done twice) and I will make sure that the formula counts the last one of the two “Completed rows”.
ActivityTimestamp
Log on | 8.11.18 0:00 |
Activity 1 | 8.12.18 0:00 |
Activity 2 | 8.13.18 0:00 |
Completed | 8.14.18 11:08 |
Completed | 8.15.18 11:32 |
Any idea?
Regards Amund
Solved! Go to Solution.
You should be able to focus in on the final datetime using LASTDATE.
The measure would be something like this:
Measure =
VAR MinDate = CALCULATE(FIRSTDATE('Table'[ActivityTimestamp]),'Table'[Activity]="Log on")
VAR MaxDate = CALCULATE(LASTDATE('Table'[ActivityTimestamp]),'Table'[Activity]="Completed")
RETURN DATEDIFF(MinDate,MaxDate,DAY)
You should be able to focus in on the final datetime using LASTDATE.
The measure would be something like this:
Measure =
VAR MinDate = CALCULATE(FIRSTDATE('Table'[ActivityTimestamp]),'Table'[Activity]="Log on")
VAR MaxDate = CALCULATE(LASTDATE('Table'[ActivityTimestamp]),'Table'[Activity]="Completed")
RETURN DATEDIFF(MinDate,MaxDate,DAY)
Hi
Thank you so much, this works!
However, I have another column in the original table which is booking number – assuming this is a repetitive process for each booking number (adding one more column = Booking number), hence I would like to get the days for each distinct bookingnumber. Do you have any suggestions how to modify the measure in order to take this element into account?
Regards Amund
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 | |
100 | |
80 | |
64 | |
58 |
User | Count |
---|---|
146 | |
110 | |
93 | |
84 | |
67 |