Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
I have a columns Start time, Stop time Location
6/14/2018 8:26:00 PM 6/15/2018 5:53:00 AM IL
The total duration is 567 minutes
I need to split duration in to hourly basis.The output I need is like below
Date Location 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24
6/14/2018 IL 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 34 60 60 60 60
6/15/2018 IL 60 60 60 60 53 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0
I want to plot a graph with
Date on x axis and hour of the day on y axis.
Any help is appreciated!
thanks
Solved! Go to Solution.
Hi @vyuvaraj,
Please check the following steps as below.
1. Enter a time table from 00:00:00-23:00:00.
2. Create a calculated table using the formula.
Datetime = ADDCOLUMNS(CROSSJOIN(CALENDAR(MAX(Table1[Start time]),MAX(Table1[Stop time])),'Time'),"datetime",[Date]+'Time'[Time])
3. Then we can create some calcualted columns to meet your requirement.
Hour = HOUR(Datetime[Time])
newdatetime = IF(Datetime[datetime]>=MAX(Table1[Start time]) && Datetime[datetime]<=MAX(Table1[Stop time]),'Datetime'[datetime])
Column = var durs = DATEDIFF(MAX(Table1[Start time]),Datetime[newdatetime],MINUTE) var dure =DATEDIFF(Datetime[newdatetime],MAX(Table1[Stop time]),MINUTE) var mind = CALCULATE(MIN(Datetime[newdatetime]),ALL(Datetime)) var maxd = CALCULATE(MAX(Datetime[newdatetime]),ALL(Datetime)) return IF(ISBLANK(Datetime[newdatetime]),BLANK(),IF(Datetime[newdatetime]=mind,durs,IF(Datetime[newdatetime]=maxd,dure,60)))
D = var datestart = DATE(YEAR(MAX(Table1[Start time])),MONTH(MAX(Table1[Start time])),DAY(MAX(Table1[Start time]))) var dateend = DATE(YEAR(MAX(Table1[Stop time])),MONTH(MAX(Table1[Stop time])),DAY(MAX(Table1[Stop time]))) var dates = DATE(YEAR(Datetime[newdatetime]),MONTH(Datetime[newdatetime]),DAY(Datetime[newdatetime])) return IF(dates=datestart,MAX(Table1[Start time]),IF(dates=dateend,MAX(Table1[Stop time])))
4. Then we can get the result as below.
For more details, please check the pbix as attached.
Regards,
Frank
Hi @vyuvaraj,
Please check the following steps as below.
1. Enter a time table from 00:00:00-23:00:00.
2. Create a calculated table using the formula.
Datetime = ADDCOLUMNS(CROSSJOIN(CALENDAR(MAX(Table1[Start time]),MAX(Table1[Stop time])),'Time'),"datetime",[Date]+'Time'[Time])
3. Then we can create some calcualted columns to meet your requirement.
Hour = HOUR(Datetime[Time])
newdatetime = IF(Datetime[datetime]>=MAX(Table1[Start time]) && Datetime[datetime]<=MAX(Table1[Stop time]),'Datetime'[datetime])
Column = var durs = DATEDIFF(MAX(Table1[Start time]),Datetime[newdatetime],MINUTE) var dure =DATEDIFF(Datetime[newdatetime],MAX(Table1[Stop time]),MINUTE) var mind = CALCULATE(MIN(Datetime[newdatetime]),ALL(Datetime)) var maxd = CALCULATE(MAX(Datetime[newdatetime]),ALL(Datetime)) return IF(ISBLANK(Datetime[newdatetime]),BLANK(),IF(Datetime[newdatetime]=mind,durs,IF(Datetime[newdatetime]=maxd,dure,60)))
D = var datestart = DATE(YEAR(MAX(Table1[Start time])),MONTH(MAX(Table1[Start time])),DAY(MAX(Table1[Start time]))) var dateend = DATE(YEAR(MAX(Table1[Stop time])),MONTH(MAX(Table1[Stop time])),DAY(MAX(Table1[Stop time]))) var dates = DATE(YEAR(Datetime[newdatetime]),MONTH(Datetime[newdatetime]),DAY(Datetime[newdatetime])) return IF(dates=datestart,MAX(Table1[Start time]),IF(dates=dateend,MAX(Table1[Stop time])))
4. Then we can get the result as below.
For more details, please check the pbix as attached.
Regards,
Frank
@v-frfei-msft this is similar to an issue I'm facing. Your solution makes sense and is the answer to the original query, but what would you change if there were multiple Locations? I have provided a sample data set below:
Start time, Stop time Location Value
6/14/2018 8:26:00 PM 6/15/2018 5:53:00 AM IL 567
6/15/2018 9:00:00 PM 6/16/2018 2:00:00 AM MC 500
6/16/2018 2:00:00 PM 6/16/2018 3:30:00 PM DE 150
I have a columns Start time, Stop time Location
6/14/2018 8:26:00 PM 6/15/2018 5:53:00 AM IL
The total duration is 567 minutes
I need to split duration in to hourly basis.The output I need is like below
Date Location 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24
6/14/2018 IL 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 34 60 60 60 60
6/15/2018 IL 60 60 60 60 53 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0
I want to plot a graph with
Date on x axis and hour of the day on y axis.
Any help is appreciated!
thanks
Hi @vyuvaraj,
Does that make sense? If so, kindly mark my answer as the solution to close the case please. Thanks in advance.
Regards,
Frank
User | Count |
---|---|
140 | |
113 | |
104 | |
77 | |
65 |
User | Count |
---|---|
136 | |
117 | |
101 | |
71 | |
61 |