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.
Hello,
I am stuck at the moment trying to calculate the difference between 2 times residing in different rows.
Basically, if the name and date are the same and there are multiple instance of in_time and out_time, I want to calculate the differencebetween the latest OUT_TIME and earliest IN_TIME.
Thank you very much in advance.
Solved! Go to Solution.
Difference = sumx(summarize(table1, Table1[Name], Table1[Date], "MaxOUT", max(Table1[OUT_TIME])),[MaxOUT])-sumx(summarize(table1, Table1[Name], Table1[Date], "MinIN", min(Table1[IN_TIME])), [MinIN])
This code creates two summary tables grouped on the name and date: one for the minimum IN_TIME and one for the maximum OUT_TIME. The IN_TIME is then subtracted from the OUT_TIME to get the difference.
This will return the difference for each Name/Date grouping for each row. If you want unique values you would need to create a summary table that was only the Name, Date and Difference columns.
Are you wanting to return the difference in a measure or in a row of the table?
Also, how do you want to represent the difference? Total hours? total minutes?
Thank you for the quick reply.
I knew I forgot something. I want the difference to show as a decimal value.
Difference = sumx(summarize(table1, Table1[Name], Table1[Date], "MaxOUT", max(Table1[OUT_TIME])),[MaxOUT])-sumx(summarize(table1, Table1[Name], Table1[Date], "MinIN", min(Table1[IN_TIME])), [MinIN])
This code creates two summary tables grouped on the name and date: one for the minimum IN_TIME and one for the maximum OUT_TIME. The IN_TIME is then subtracted from the OUT_TIME to get the difference.
This will return the difference for each Name/Date grouping for each row. If you want unique values you would need to create a summary table that was only the Name, Date and Difference columns.
Thank you very much.
As a beginner, you really saved me a ton of time and headaches.
Have a great day!!!
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 |
---|---|
113 | |
97 | |
80 | |
69 | |
59 |
User | Count |
---|---|
150 | |
119 | |
104 | |
87 | |
67 |