Forum Discussion
Helper IICheck if Asset is in a Location between two dates
ok I have a table "Fleetsu Time Asset In Fence" and has columns Ctc_FleetNo,Entered_EventDateStamp , Exited_EventDateStamp and FenceName and the DateDIM table has date column , I need to know what all trucks(Ctc_FleetNo) are in the fence(FenceName) between Entered_EventDateStamp and Exited_EventDateStamp when filtering by Date from DateDIM, so need a dax formula for it , For example if a Truck123 from column Ctc_FleetNo enters a FenceName on 12/05/2023 and exits on 18/05/2023 then when i filter by date from DateDIM for example like 15/05/2023 then it should show that Truck123 is in that fence,
the DateDMIN date column is connected to the "Fleetsu Time Asset In Fence" column Entered_EventDateStamp
Hi skv17
You can consider to remove the relationship, then create the following measure
Measure 2 = CALCULATE(SUM('Table'[Sum of Duration in Fence Minutes]),FILTER('Table','Table'[Entered_EventDateStamp]<=MAX('Date'[Date])&&'Table'[Exited_EventTimeStamp]>MAX('Date'[Date])))Output
Best Regards!
Yolo Zhu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
11 Replies
- Ritaf1983
Super User
Hi skv17
Please provide sample data that covers your issue or question completely, in a usable format (not as a screenshot).
https://community.powerbi.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-Forum/ba-...
Please show the expected outcome based on the sample data you provided.
https://community.powerbi.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447523
skv17tc_FleetNo Licence Plate FenceName Entered_EventDateStamp Entered_EventTimeStamp Exited_EventTimeStamp Sum of Duration in Fence Minutes PM326 1GJL589 Depot Hazelmere-Depot1 10/09/2023 0:00 10/09/2023 23:03 10/09/2023 23:25 22 PM701 1HKE056 Depot Hazelmere-Depot1 10/09/2023 0:00 10/09/2023 20:51 11/09/2023 10:52 841 PM309 1GJK770 Depot Hazelmere-Depot1 10/09/2023 0:00 10/09/2023 20:47 11/09/2023 6:57 610 PM309 1GJK770 Depot Hazelmere-Depot1 10/09/2023 0:00 10/09/2023 20:00 10/09/2023 20:12 12 PM309 1GJK770 Depot Hazelmere-Depot1 10/09/2023 0:00 10/09/2023 19:15 10/09/2023 19:26 11 PM309 1GJK770 Depot Hazelmere-Depot1 10/09/2023 0:00 10/09/2023 18:13 10/09/2023 18:20 7 PM309 1GJK770 Depot Hazelmere-Depot1 10/09/2023 0:00 10/09/2023 15:47 10/09/2023 16:00 13 PM326 1GJL589 Depot Hazelmere-Depot1 10/09/2023 0:00 10/09/2023 14:29 10/09/2023 23:02 513 PM326 1GJL589 Depot Hazelmere-Depot1 10/09/2023 0:00 10/09/2023 14:15 10/09/2023 14:28 13 PM309 1GJK770 Depot Hazelmere-Depot1 10/09/2023 0:00 10/09/2023 13:28 10/09/2023 13:38 10
- amitchandak
skv17 , To me seem like very similar to HR or between date problem
Power BI: HR Analytics - Employees as on Date : https://youtu.be/e6Y-l_JtCq4
https://community.powerbi.com/t5/Community-Blog/HR-Analytics-Active-Employee-Hire-and-Termination-trend/ba-p/882970
Power BI HR Active Employee Tenure Bucketing, and Hired, Terminated, and Active employees: https://youtu.be/fvgcx8QLqZUPower BI Dax Measure- Allocate data between Range: https://youtu.be/O653vwLTUzM
- skv17
how do I dispaly the list of assets, as its a measure and it will only give the headline figure of how many assets are in the location.
- skv17
Ctc_FleetNo Licence Plate FenceName Entered_EventDateStamp Entered_EventTimeStamp Exited_EventTimeStamp Sum of Duration in Fence Minutes PM326 1GJL589 Depot Hazelmere-Depot1 10/09/2023 0:00 10/09/2023 23:03 10/09/2023 23:25 22 PM701 1HKE056 Depot Hazelmere-Depot1 10/09/2023 0:00 10/09/2023 20:51 11/09/2023 10:52 841 PM309 1GJK770 Depot Hazelmere-Depot1 10/09/2023 0:00 10/09/2023 20:47 11/09/2023 6:57 610 PM309 1GJK770 Depot Hazelmere-Depot1 10/09/2023 0:00 10/09/2023 20:00 10/09/2023 20:12 12 PM309 1GJK770 Depot Hazelmere-Depot1 10/09/2023 0:00 10/09/2023 19:15 10/09/2023 19:26 11 PM309 1GJK770 Depot Hazelmere-Depot1 10/09/2023 0:00 10/09/2023 18:13 10/09/2023 18:20 7 PM309 1GJK770 Depot Hazelmere-Depot1 10/09/2023 0:00 10/09/2023 15:47 10/09/2023 16:00 13 PM326 1GJL589 Depot Hazelmere-Depot1 10/09/2023 0:00 10/09/2023 14:29 10/09/2023 23:02 513 PM326 1GJL589 Depot Hazelmere-Depot1 10/09/2023 0:00 10/09/2023 14:15 10/09/2023 14:28 13 PM309 1GJK770 Depot Hazelmere-Depot1 10/09/2023 0:00 10/09/2023 13:28 10/09/2023 13:38 10 - skv17
this is the main table which has the entered and exited event
- skv17
Date Day of Year FiscalMonth FiscalMonthLong MonthYear MonthYearLong Quarter MonthYearNum Week of Year WeekDate MonthNum MonthLong Month FutureDate Flag_YTD Flag_QTD FiscalYear FiscalQuarter Flag_MTD FiscalMonthNum CurYearOffset CurWeekOffset CurrentDayOffset CurMonthOffset CurFiscalYearOffset CurQuarterOffset Weekday WeekdayNum WeekdayWeekend WeekSequenceNum Year ######## 247 Sep September Sep-23 Sep-23 Q3 202309 37 ######## 9 September Sep Past YTD QTD FY24 FQ1 MTD 3 0 -1 -9 0 0 0 Mon 0 Weekend 5 2023 ######## 248 Sep September Sep-23 Sep-23 Q3 202309 37 ######## 9 September Sep Past YTD QTD FY24 FQ1 MTD 3 0 -1 -8 0 0 0 Tue 1 Weekday 5 2023 ######## 249 Sep September Sep-23 Sep-23 Q3 202309 37 ######## 9 September Sep Past YTD QTD FY24 FQ1 MTD 3 0 -1 -7 0 0 0 Wed 2 Weekday 5 2023 ######## 250 Sep September Sep-23 Sep-23 Q3 202309 37 ######## 9 September Sep Past YTD QTD FY24 FQ1 MTD 3 0 -1 -6 0 0 0 Thu 3 Weekday 5 2023 ######## 251 Sep September Sep-23 Sep-23 Q3 202309 37 ######## 9 September Sep Past YTD QTD FY24 FQ1 MTD 3 0 -1 -5 0 0 0 Fri 4 Weekday 5 2023 ######## 252 Sep September Sep-23 Sep-23 Q3 202309 37 ######## 9 September Sep Past YTD QTD FY24 FQ1 MTD 3 0 -1 -4 0 0 0 Sat 5 Weekday 5 2023 ######## 253 Sep September Sep-23 Sep-23 Q3 202309 37 ######## 9 September Sep Past YTD QTD FY24 FQ1 MTD 3 0 -1 -3 0 0 0 Sun 6 Weekend 5 2023 - skv17
this is the date table
Hi skv17
You can consider to remove the relationship, then create the following measure
Measure 2 = CALCULATE(SUM('Table'[Sum of Duration in Fence Minutes]),FILTER('Table','Table'[Entered_EventDateStamp]<=MAX('Date'[Date])&&'Table'[Exited_EventTimeStamp]>MAX('Date'[Date])))Output
Best Regards!
Yolo Zhu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
