Calculate Before and After 6:00 pm Hours

[GerardX]

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

[Ashish_Mathur]

Hi,

Try this calculated column formula

=IFERROR(TIME(18,0,0)-Data[End Time],0)

Hope this helps.

[GerardX]

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.

[Ashish_Mathur]

Hi,

I do not understand your question.  Please show the expected result.

[GerardX]

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.

[Zubair_Muhammad]

@GerardX

After 18 Hours =
VAR resut =
    IF ( HOUR ( Table1[End Time] ) < 18, 0, Table1[End Time] - 18 / 24 )
RETURN
    FORMAT ( resut, "HH : MM : SS" )

[GerardX]

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..

[Ashish_Mathur]

Hi,

You may download my PBI file from here.

Hope this helps.

[GerardX]

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?

[Ashish_Mathur]

Hi,

You may download my file from here.

Hope this helps.

[GerardX]

Hi,

Can you please explain what you changed from the first .pbix?  

The total is not what is expected? Thanks!

[Ashish_Mathur]

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.

[GerardX]

Hi,

Thank you. I see the difference and really appreciate your time.

What is the easiest way to total these columns up?

[Ashish_Mathur]

Hi,

My formula shows the total as well.

[GerardX]

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.

[Ashish_Mathur]

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.

[GerardX]

Please see PBI file here

Thank you.

[Ashish_Mathur]

Hi,

I cannot get it right.  May be someone else will help you.

[GerardX]

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.

[GerardX]

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.