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've got a table with a bunch of rows with columns for MachineID(int), StartTime(DateTime), and EndTime(DateTime). I want to get the total amount of time falling inside at least one of the time ranges for each entry, for a given MachineID.
Is there a way to create a Measure (mapping to MachineID) that does this? I can think of a way to do this with code, but searching through DAX functions doesn't seem to be returning anything useful.
Solved! Go to Solution.
Hi,
If you include the beginning and ending dates, the results should be 59 and 21. You may download my PBI file from here.
Hope this helps.
Hi,
Share some data and show the expected result.
So for
MachineID | StartTime | EndTime |
1 | 1/3/2021 0:00:00 | 1/9/2021 0:00:00 |
1 | 1/3/2021 0:00:00 | 1/15/2021 0:00:00 |
1 | 3/4/2021 0:00:00 | 3/11/2021 0:00:00 |
1 | 3/6/2021 0:00:00 | 3/7/2021 0:00:00 |
1 | 3/10/2021 0:00:00 | 4/18/2021 0:00:00 |
2 | 1/3/2021 0:00:00 | 1/7/2021 0:00:00 |
2 | 2/5/2021 0:00:00 | 2/20/2021 0:00:00 |
the output would be
MachineID | TimeEncapsulated(days) |
1 | 57 |
2 | 19 |
How did you arrive at 57 and 19? Explain.
The 57 comes from the total days covered by the date ranges covering 1/3 to 1/15 and 3/4 to 4/18 for machine 1. The 19 comes from total days covered by the two non-overlapping date ranges for machine 2.
Hi,
If you include the beginning and ending dates, the results should be 59 and 21. You may download my PBI file from here.
Hope this helps.
Your PBI file doesn't indicate how "Date" was generated from "StartTime" and "EndTime". Did you generate a new table by adding an entry for every day that fell within the range for a given machine ID, or something else?
Hi,
Please study the steps in the Query Editor.
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 |
---|---|
115 | |
99 | |
86 | |
70 | |
62 |
User | Count |
---|---|
151 | |
120 | |
103 | |
87 | |
68 |