Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hello everyone,
my situation is the following. I have a parking that only sends changing states.
It sends a "1" when a car arrived at the parking, then it doesn't send anything until the car leaves again. At that moment the parking sends a "0". I did already the calculation to see the current status but now I need to do analysis over a long time.
So I should be able to see how many hours per day the parking was taken and how many hours it was free.
The data looks like this:
Parking-ID | DateTime | Status | Comment |
10 | 20.12.2019 16:35 | 0 | Car left |
10 | 20.12.2019 08:22 | 1 | Car arrived |
10 | 19.12.2019 22:47 | 0 | Car left |
10 | 19.12.2019 19:31 | 1 | Car arrived |
10 | 19.12.2019 17:40 | 0 | Car left |
10 | 19.12.2019 07:49 | 1 | Car arrived |
How can I calculate per day how many hours the parking was taken and how many hours it was free?
Special request: The hours between 23:00 and 05:00 shouldn't count in this anylsis, how am I doing that?
Thank you and best regards
Solved! Go to Solution.
Hi @selimovd
Some changes are required indeed when using multiple Parking-ID's. The first step is the same; make sure you sort by DateTime and add an Index column. Then add a calculated column that adds a ranking per ParkingID based on Index (which reflects the order of Date):
RankedIndex =
VAR _curParkID = 'Table'[Parking-ID]
RETURN
RANKX(FILTER('Table', 'Table'[Parking-ID] = _curParkID), 'Table'[Index], , ASC)
THen change the calculated Table to this:
ParkingActivityMinutes =
GENERATE('Table',
VAR _curParkID = 'Table'[Parking-ID]
VAR _startperiod = 'Table'[DateTime]
VAR _curRankedIndex = 'Table'[RankedIndex]
VAR _nextRankedIndex = IF(COUNTROWS(FILTER(ALL('Table'), 'Table'[RankedIndex] > _curRankedIndex)) > 0, _curRankedIndex + 1, _curRankedIndex)
VAR _endperiod = CALCULATE(MAX('Table'[DateTime]), FILTER(ALL('Table'), 'Table'[RankedIndex] = _nextRankedIndex && 'Table'[Parking-ID] = _curParkID))
RETURN
GENERATESERIES(_startperiod, _endperiod, TIME(0,1,0)))
Now this adds up to 3800 rows or something by using twice your example table. You might want to consider using the calculated table as an expression in a aggregated table or something. The logic remains te same 🙂
Kind regards
Djerro123
-------------------------------
If this answered your question, please mark it as the Solution. This also helps others to find what they are looking for.
Keep those thumbs up coming! 🙂
Proud to be a Super User!
Hello @selimovd
i think its almost impossible to do such a calculation in DAX. To analysis this data I think it's needed to do some transforming before. Try out this code in Power Query and tell me if this could suit you (formatting it's a desaster.. I quickly registered with the GUI)
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("jc9BCoMwFIThq0jWIm/GVJu37THEhdAIgqsgnr8pFC2RSLbDx8A/DAZiakNpwIYCV6HT9hGn7/yaQrX6eTNjfYXyVDJO+MEphGX379PCHZZU22dP/yCctig8Ra9WSk4lQpc5ZWl+Cu/yWZqfwrv8i83lpzCbP34A", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [#"Parking-ID" = _t, DateTime = _t, Status = _t, Comment = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Parking-ID", Int64.Type}, {"DateTime", type datetime}, {"Status", Int64.Type}, {"Comment", type text}}),
#"Sorted Rows" = Table.Sort(#"Changed Type",{{"Parking-ID", Order.Ascending}, {"DateTime", Order.Ascending}}),
#"Grouped Rows" = Table.Group(#"Sorted Rows", {"Parking-ID"}, {{"AllRows", each _, type table [#"Parking-ID"=number, DateTime=datetime, Status=number, Comment=text]}}),
#"Added Custom" = Table.AddColumn(#"Grouped Rows", "ListDateTimes", each Table.FromColumns({ List.DateTimes(List.Min([AllRows][DateTime]), Duration.TotalMinutes(List.Max([AllRows][DateTime])-List.Min([AllRows][DateTime]))+1,#duration(0,0,1,0))})),
#"Added Custom1" = Table.AddColumn(#"Added Custom", "TableJoin", each Table.FillDown( Table.Join([AllRows],"DateTime", [ListDateTimes],"Column1",JoinKind.FullOuter),{"Parking-ID", "DateTime","Status","Comment"})),
#"Removed Other Columns" = Table.SelectColumns(#"Added Custom1",{"TableJoin"}),
#"Expanded TableJoin" = Table.ExpandTableColumn(#"Removed Other Columns", "TableJoin", {"Parking-ID", "Status", "Comment", "Column1"}, {"Parking-ID", "Status", "Comment", "Column1"}),
#"Changed Type2" = Table.TransformColumnTypes(#"Expanded TableJoin",{{"Column1", type datetime}}),
#"Renamed Columns" = Table.RenameColumns(#"Changed Type2",{{"Column1", "DateTime"}}),
#"Added Custom2" = Table.AddColumn(#"Renamed Columns", "Minute", each 1),
#"Changed Type1" = Table.TransformColumnTypes(#"Added Custom2",{{"Minute", Int64.Type}}),
#"Added Custom3" = Table.AddColumn(#"Changed Type1", "Time", each Time.From([DateTime])),
#"Changed Type3" = Table.TransformColumnTypes(#"Added Custom3",{{"Time", type time}}),
#"Added Custom4" = Table.AddColumn(#"Changed Type3", "Timeperiod to evaluated", each if Time.Hour([Time])>=23 or Time.Hour([Time])<=5 then false else true)
in
#"Added Custom4"
The result looks like this
the calculated indicator "Timerperiod to evaluated" checks if the time it's not after 23 or before 5.
Copy paste this code to the advanced editor to see how the solution works
If this post helps or solves your problem, please mark it as solution.
Kudos are nice to - thanks
Have fun
Jimmy
Coincidentally, just yesterday I complimented @TomMartens on providing a elegent solution on a similar problem, so credits are for him 😉
First, sort on column DateTime in the PowerQuery Editor (from old to newest) and then add an Index column. Your table now looks like this:
Now we are going to create a generated Table, that creates rows for every minute the care is in a certain state. The DAX for this is the following:
ParkingActivityMinutes =
GENERATE(ParkingActivity,
VAR _startperiod = ParkingActivity[DateTime]
VAR _curIndex = ParkingActivity[Index]
VAR _nextIndex = IF(COUNTROWS(FILTER(ALL(ParkingActivity), ParkingActivity[Index] > _curIndex)) > 0, _curIndex + 1, _curIndex)
VAR _endperiod = CALCULATE(MAX(ParkingActivity[DateTime]), FILTER(ALL(ParkingActivity), ParkingActivity[Index] = _nextIndex))
RETURN
GENERATESERIES(_startperiod, _endperiod, TIME(0,1,0)))
We are combining two tables here, the original table and a newly generated table (with GENERATESERIES). Now you have the following table:
First few rows of the newly created table.
Rows where a status changed from 1 to 0
Now you can create a measure for calculating the minutes by summing column [Status] in a certain date/time range. The Measure is:
ParkingTaken = SUM(ParkingActivityMinutes[Status])
Let me know if this works for you! 🙂
Kind regards
Djerro123
-------------------------------
If this answered your question, please mark it as the Solution. This also helps others to find what they are looking for.
Keep those thumbs up coming! 🙂
Proud to be a Super User!
Thank you already for your approach @djerro123 !
In general for this example it works pretty well. The problem is when I come back to my real world case, that has more parking IDs. As there is more then one parking ID in the data the index is mixing between the Ids and it doesn't work anymore.
Do you have any solution when I have rows from multiple parking IDs?
Thank you and best regards
Hello @selimovd
were you able to solve the problem with any reply given?
If so, please mark it as solution (to help other users find useful content and to acknowledge the work of users that helped you)
Kudoes are nice too
All the best
Jimmy
Hi @selimovd
Some changes are required indeed when using multiple Parking-ID's. The first step is the same; make sure you sort by DateTime and add an Index column. Then add a calculated column that adds a ranking per ParkingID based on Index (which reflects the order of Date):
RankedIndex =
VAR _curParkID = 'Table'[Parking-ID]
RETURN
RANKX(FILTER('Table', 'Table'[Parking-ID] = _curParkID), 'Table'[Index], , ASC)
THen change the calculated Table to this:
ParkingActivityMinutes =
GENERATE('Table',
VAR _curParkID = 'Table'[Parking-ID]
VAR _startperiod = 'Table'[DateTime]
VAR _curRankedIndex = 'Table'[RankedIndex]
VAR _nextRankedIndex = IF(COUNTROWS(FILTER(ALL('Table'), 'Table'[RankedIndex] > _curRankedIndex)) > 0, _curRankedIndex + 1, _curRankedIndex)
VAR _endperiod = CALCULATE(MAX('Table'[DateTime]), FILTER(ALL('Table'), 'Table'[RankedIndex] = _nextRankedIndex && 'Table'[Parking-ID] = _curParkID))
RETURN
GENERATESERIES(_startperiod, _endperiod, TIME(0,1,0)))
Now this adds up to 3800 rows or something by using twice your example table. You might want to consider using the calculated table as an expression in a aggregated table or something. The logic remains te same 🙂
Kind regards
Djerro123
-------------------------------
If this answered your question, please mark it as the Solution. This also helps others to find what they are looking for.
Keep those thumbs up coming! 🙂
Proud to be a Super User!
Ok I just tried this I was able to get the Ranked Index to work but the Calculated table does not seem to work. I keep getting this error
The expression refers to multiple columns. Multiple columns cannot be converted to a scalar value.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
47 | |
24 | |
20 | |
15 | |
13 |
User | Count |
---|---|
51 | |
46 | |
39 | |
19 | |
19 |