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,
Just recently started using Power BI, I have a table with Start Time and End Time and need to calculate
hours Before and After 6:00 pm.
Start Time End Time Before Six After Six
3:00 pm 5:00 pm 2 0
6:00 pm 11:00 pm 0 5
4:00 pm 7:00 pm 2 1
It is the hours crossing over 6:00 pm I am having difficult with, any help would be appreciated.
Thanks
Hi,
Try this calculated column formula
=IFERROR(TIME(18,0,0)-Data[End Time],0)
Hope this helps.
Thank you! This has started me down a better path, it think.
I am trying to use the calculated column for both Before and After 6:00?
After 18:00 Hours =IFERROR(TIME(18,0,0)-Data[End Time],0)
Before 18:00 Hours =IFERROR(TIME(18,0,0)-Data[Start Time],0)
See sample results:
Start Time End Time Before 18:00 Hours After 18:00 Hours
8:00:00 18:30:00 10:00 00:30
12:00:00 18:00:00 6:00 00:00
15:30:00 16:30:00 2:30 01:30
18:00:00 21:00:00 00:00 03:00
20:00:00 22:30:00 02:00 04:30
The Start Time and End Time could cross over 18:00:00, if it is not applicable it should not calculate. How do I take that in consideration?
Thank you for your help.
Hi,
I do not understand your question. Please show the expected result.
Hi,
My objective is to get total hours separately: Before 18:00 and After 18:00 timerange from one span of Start and End time.
Expected result below.
Start Time End Time Before 18:00 Hours After 18:00 Hours
17:00:00 19:00:00 1 1
8:00:00 18:30:00 10 0.30
12:00:00 18:00:00 6 0
15:30:00 16:30:00 2:30 0
18:00:00 21:00:00 0 3
20:00:00 22:30:00 0 2:30
__________________________________________________________________
total 19:30 7:00
Before 18:00: timeframe from 2:00 to 18:00
After 18:00: timeframe from 18:00 to 2:00
Sorry if I am unclear, and thank you for your help.
After 18 Hours = VAR resut = IF ( HOUR ( Table1[End Time] ) < 18, 0, Table1[End Time] - 18 / 24 ) RETURN FORMAT ( resut, "HH : MM : SS" )
Thank you! I am really new to PBI desktop, I know it shows.
I am now able to get "After 18 Hours" column with correct time values in Time data type.
What is the best way to sum this column, I have tried to change to decimal and also to text but getting a bit confused with results?
Also, to get the "Before 18:00 Hours" sum, I am going subtract sum of "After 18 Hours" from "Total Hours", seems logical to me but that is not saying much today.
I appreciate any help or insight..
Hi,
You may download my PBI file from here.
Hope this helps.
Thank you.
Is it possible to get actual duration of Start Time and End Time with your measure?
I modified PBI file for example:
Also, total does not seem expand to accomodate large numbers or extra digits?
Hi,
You may download my file from here.
Hope this helps.
Hi,
Can you please explain what you changed from the first .pbix?
The total is not what is expected? Thanks!
Hi,
Open both files and compare the formulas yourself. If there is a mistake in the file that i shared with you, then let me know.
Hi,
Thank you. I see the difference and really appreciate your time.
What is the easiest way to total these columns up?
Hi,
My formula shows the total as well.
Hi,
The total is not calculating right, the columns have over 200,000 rows. Do I need to the change data format on totals? Thank you.
I can't say why. I need to ee the file. Imlement my formula in your file and share the link from where i can download your PBI file.
Hi,
I cannot get it right. May be someone else will help you.
Thanks for all your help....do you think this article pertains?
https://community.powerbi.com/t5/DAX-Commands-and-Tips/Dealing-with-Measure-Totals/td-p/63376
I am not sure which way to go.
Thanks to Ashish, we are able to get columns of time Before and After 18:00 from a Start and End Time.
I have now stalled out trying to total these columns up. The column totals are wrong. Any help or point in right direction, I have been working with wrapping measure in hasonefilter and also trying to convert columns to different datatypes but am really new to PBI Desktop and am stuck.
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 |