Forum Discussion
Calculate the specific hours between two dates
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!
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
5 Replies
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.
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."
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
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.
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
