Calculate the specific hours between two dates

[SebaSpotti]

Hi all!

I have two dates in the format "dd/mm/yyyy hh:mm:ss". I want to calculate which hours there are between the two dates.

For example, if the dates are "01/01/2024 12:00:00" and "01/01/2024 18:00:00" i want to have back these hours "12","13","14","15","16","17","18". [Note that the gap between the two dates is not larger than 24h]

Everithing is for creating an histogram in which visualize which hours are more often "touched".

I think about creating 24 columns and fill them with 0 or 1 if the corresponding hour is inclueded or not. The only little problem is that the 2 dates can overlap in 2 days so it could be a little more difficult to calculate the columns (but not impossibile at all).

Do you have any suggestions?

Thank you all!

[Anonymous · Accepted solution]

Hi  @SebaSpotti ,

You can try using matrix visual for this requirement

Here are the steps you can follow：

1. Create calculated table.

Table 2 =
var _table=
GENERATESERIES (DATE (2024, 01, 01), NOW(), 0.041666667)
return
ADDCOLUMNS(
    _table,"Hour",HOUR([Value]))

Table 3 =
DISTINCT(
    'Table 2'[Hour])

2. Create measure.

Measure =
var _date=
MAXX(
    FILTER(ALL('Table'),
    'Table'[Date]<MAX('Table'[Date])),[Date])
var _if=
IF(
    _date=BLANK(),BLANK(),
CONCATENATEX(
    FILTER(ALL('Table 2'),
    'Table 2'[Value]>=_date&&'Table 2'[Value]<=MAX('Table'[Date])),[Hour],"丶"))
return
IF(
    CONTAINSSTRING(
        _if,MAX('Table 3'[Hour]))=TRUE(),1,0)

3. Result:

Best Regards,

Liu Yang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

[Anonymous]

Thanks for the reply from @bhanu_gautam , please allow me to provide another insight:
Hi  @SebaSpotti ,

Here are the steps you can follow：

1. Create calculated table.

Table 2 =
var _table=
GENERATESERIES (DATE (2024, 01, 01), NOW(), 0.041666667)
return
ADDCOLUMNS(
    _table,"Hour",HOUR([Value]))

2. Create calculated column.

Test =
var _date=
MAXX(
    FILTER(ALL('Table'),
    'Table'[Date]<EARLIER('Table'[Date])),[Date])
return
IF(
    _date=BLANK(),BLANK(),
CONCATENATEX(
    FILTER(ALL('Table 2'),
    'Table 2'[Value]>=_date&&'Table 2'[Value]<='Table'[Date]),[Hour],"丶"))

3. Result:

Best Regards,

Liu Yang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

[SebaSpotti]

Thank you @Anonymous . I appreciate your answer. The only thing, that may I have specified wrong in the main question, is that a do not want a column "Test" as in your example, but a 24 columns, one for each hour, filled with 1 if the hour is between the two considered data, 0 if not.

[bhanu_gautam]

@SebaSpotti , You can create a calculated column for Hours between date

First make sure you have Date Table

DateTable =
ADDCOLUMNS (
CALENDAR ( DATE ( 2024, 1, 1 ), DATE ( 2024, 12, 31 ) ),
"Hour", HOUR ( [Date] )
)

Then create a calculated column

HoursBetween =
VAR StartDateTime = [StartDate]
VAR EndDateTime = [EndDate]
VAR StartHour = HOUR(StartDateTime)
VAR EndHour = HOUR(EndDateTime)
VAR DateDiff = DATEDIFF(StartDateTime, EndDateTime, HOUR)
RETURN
IF (
DateDiff <= 24,
GENERATESERIES(StartHour, EndHour, 1),
BLANK()
)

Unpivot the Hours: If you have created 24 columns for each hour, you need to unpivot these columns to create a single column with all the hours.

Create a Histogram: Use the unpivoted hours column to create a histogram.

[SebaSpotti]

I tried to follow your path but while creating the new column I get an error "A table of multiple values was specified when a single value was expected."