Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Hi all,
I have a problem with date ranges.
In my table each row contains an activity code as well as a start time and an end time in Date/Time/Timezone format like this:
Start time | End time | Activity |
09.03.2018 23:30:00 +00:00 | 11.03.2018 04:00:00 +00:00 | A |
For the report I need to calculate the duration per Activity per day.
The result for activity A should be
09.03.2018 00:30
10.03.2018 24:00
11.03.2018 04:00
Is there a way I can do this without splitting the rows via the query editor? I am not very experienced with M...
Solved! Go to Solution.
True, haven't though about that. So we have to add some conditions like so:
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjDWNTTUNTIwtFAwMLIyNrAyMFDSQRE1AQpBRB2VYmMB", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [#"Start time" = _t, #"End time" = _t, Activity = _t]), ChgType = Table.TransformColumnTypes(Source,{{"Start time", type datetime}, {"End time", type datetime}, {"Activity", type text}}), ListOfDates = Table.AddColumn(ChgType, "Dates", each List.Transform({Number.From(Date.From([Start time]))..Number.From(Date.From([End time]))}, Date.From)), ListOfDurations = Table.AddColumn(ListOfDates, "Duration", each if Date.From([Start time]) = Date.From([End time]) then {[End time]-DateTime.From([Start time])} else {DateTime.From(Date.AddDays([Dates]{0},1))-DateTime.From([Start time])} & (try List.Repeat({#duration(1,0,0,0)}, List.Count([Dates])-2) otherwise {}) & {DateTime.From([End time])-DateTime.From(List.Last([Dates]))}), // Duration part of last day Result = Table.AddColumn(ListOfDurations, "Result", each Table.FromColumns({[Dates],[Duration]}, {"Date", "Duration"})), #"Expanded Result" = Table.ExpandTableColumn(Result, "Result", {"Date", "Duration"}, {"Result.Date", "Result.Duration"}) in #"Expanded Result"
Imke Feldmann (The BIccountant)
If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!
How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries
I feel like I am getting closer but not really sure. This will generate the desired table for an individual item.
Table = VAR startDateTime = MAX(IncidentHours[Start Time]) VAR startDate = DATE(YEAR(startDateTime),MONTH(startDateTime),DAY(startDateTime)) VAR endDateTime = MAX(IncidentHours[End Time]) VAR endDate = DATE(YEAR(endDateTime),MONTH(endDateTime),DAY(endDateTime)) VAR tmpCalendar = CALENDAR(startDateTime,endDateTime) VAR tmpCalendar1 = ADDCOLUMNS(tmpCalendar,"Minutes",IF(startDate=[Date],ABS(DATEDIFF([Date]+1,startDateTime,MINUTE)), IF(endDate=[Date],ABS(DATEDIFF(endDateTime,[Date],MINUTE)), 24*60 ) )) RETURN tmpCalendar1
However, I have a feeling that is not exactly what you want. The basic problem here is that you have to "invent" days basically for this incident. Therefore, you can't really tie a date table to it which makes it incredibly problematic to display.
I almost feel like you really have to solve this problem by starting in Power Query (M) and generate a table that has a row by incident for every day that it exists. So,
3/9/2018,A
3/10/2018,A
3/11/2018,A
3/10/2018,B
3/11/2018,B
3/12/2018,B
Then you could relate this to your incident table and things would be vastly easier. I'm just not certain DAX is suited for this one. Perhaps @ImkeF or @Vvelarde have an idea on how you could generate that kind of table in Power Query from the data table you have shown above.
You could do that like so:
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjDWNbDUNTIwtFAwMrYyNrAyMFDSAYkaGkJEDUyAQhBRR6XYWAA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [#"Start time" = _t, #"End time" = _t, Activity = _t]), ChgType = Table.TransformColumnTypes(Source,{{"Start time", type datetime}, {"End time", type datetime}, {"Activity", type text}}), ListOfDates = Table.AddColumn(ChgType, "Dates", each List.Transform({Number.From(Date.From([Start time]))..Number.From(Date.From([End time]))}, Date.From)), ListOfDurations = Table.AddColumn(ListOfDates, "Duration", each {DateTime.From([Dates]{1})-DateTime.From([Start time])} & // Duration part of first day List.Repeat({#duration(1,0,0,0)}, List.Count([Dates])-2) & // 24 hours for each day in between {DateTime.From([End time])-DateTime.From(List.Last([Dates]))}), // Duration part of last day Result = Table.AddColumn(ListOfDurations, "Result", each Table.FromColumns({[Dates],[Duration]}, {"Date", "Duration"})) in Result
Imke Feldmann (The BIccountant)
If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!
How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries
That looks great!
But it is not working if Start time and End time are on the same date... It leads to a count argument -1
Hi Pierre-Se, can you please share your solution to this challenge? I have the exact requirements and have not been able to find a solution yet.
True, haven't though about that. So we have to add some conditions like so:
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjDWNTTUNTIwtFAwMLIyNrAyMFDSQRE1AQpBRB2VYmMB", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [#"Start time" = _t, #"End time" = _t, Activity = _t]), ChgType = Table.TransformColumnTypes(Source,{{"Start time", type datetime}, {"End time", type datetime}, {"Activity", type text}}), ListOfDates = Table.AddColumn(ChgType, "Dates", each List.Transform({Number.From(Date.From([Start time]))..Number.From(Date.From([End time]))}, Date.From)), ListOfDurations = Table.AddColumn(ListOfDates, "Duration", each if Date.From([Start time]) = Date.From([End time]) then {[End time]-DateTime.From([Start time])} else {DateTime.From(Date.AddDays([Dates]{0},1))-DateTime.From([Start time])} & (try List.Repeat({#duration(1,0,0,0)}, List.Count([Dates])-2) otherwise {}) & {DateTime.From([End time])-DateTime.From(List.Last([Dates]))}), // Duration part of last day Result = Table.AddColumn(ListOfDurations, "Result", each Table.FromColumns({[Dates],[Duration]}, {"Date", "Duration"})), #"Expanded Result" = Table.ExpandTableColumn(Result, "Result", {"Date", "Duration"}, {"Result.Date", "Result.Duration"}) in #"Expanded Result"
Imke Feldmann (The BIccountant)
If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!
How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries
Thanks! I just solved it on my own with the same condition
I will now run it with my DB and see if everything works there as well.
This is as far as I have gotten on this one. Will tinker with it some more maybe:
Create these four columns:
StartDate = [Start Time].[Date] EndDate = [End Time].[Date] StartDiff = ABS(DATEDIFF([StartDate]+1,IncidentHours[Start Time],MINUTE)) EndDiff = ABS(DATEDIFF([EndDate],IncidentHours[End Time],MINUTE))
You can create this measure for days that are "skipped" provided you have an unrelated Calendar table:
Hours = VAR myStart = MIN('Calendar'[Date]) VAR myEnd = MAX('Calendar'[Date]) + 1 VAR myEndStart = MAX('Calendar'[Date]) VAR OpenAllDay = COUNTROWS(FILTER(ALL(IncidentHours),([Start Time]<myStart&&[End Time]>myEnd))) RETURN IF(OpenAllDay,24*60,0)
Everything is returned in minute which you would have to convert to duration format using something like this:
https://community.powerbi.com/t5/Community-Blog/Aggregating-Duration-Time/ba-p/22486
Haven't figured out a way to put it all together yet.
User | Count |
---|---|
139 | |
113 | |
103 | |
73 | |
63 |
User | Count |
---|---|
136 | |
125 | |
107 | |
70 | |
61 |