Skip to main content
cancel
Turn on suggestions
Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.
Showing results for 
Search instead for 
Did you mean: 

The Power BI Data Visualization World Championships is back! Get ahead of the game and start preparing now! Learn more

Reply
skv17
Helper II
Helper II

Check if Asset is in a Location between two dates

‎09-14-2023 08:58 PM


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

Labels:
Message 1 of 12
1,137 Views
0
1 ACCEPTED SOLUTION
Anonymous
Not applicable
In response to skv17

‎09-17-2023 11:23 PM

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

vxinruzhumsft_0-1695018111402.png

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.

View solution in original post

Check if Asset is in a Location between two dates.pbix
Message 10 of 12
1,044 Views
11 REPLIES 11
skv17
Helper II
Helper II

‎09-14-2023 09:59 PM
DateDay of YearFiscalMonthFiscalMonthLongMonthYearMonthYearLongQuarterMonthYearNumWeek of YearWeekDateMonthNumMonthLongMonthFutureDateFlag_YTDFlag_QTDFiscalYearFiscalQuarterFlag_MTDFiscalMonthNumCurYearOffsetCurWeekOffsetCurrentDayOffsetCurMonthOffsetCurFiscalYearOffsetCurQuarterOffsetWeekdayWeekdayNumWeekdayWeekendWeekSequenceNumYear
########247SepSeptemberSep-23Sep-23Q320230937########9SeptemberSepPastYTDQTDFY24FQ1MTD30-1-9000Mon0Weekend52023
########248SepSeptemberSep-23Sep-23Q320230937########9SeptemberSepPastYTDQTDFY24FQ1MTD30-1-8000Tue1Weekday52023
########249SepSeptemberSep-23Sep-23Q320230937########9SeptemberSepPastYTDQTDFY24FQ1MTD30-1-7000Wed2Weekday52023
########250SepSeptemberSep-23Sep-23Q320230937########9SeptemberSepPastYTDQTDFY24FQ1MTD30-1-6000Thu3Weekday52023
########251SepSeptemberSep-23Sep-23Q320230937########9SeptemberSepPastYTDQTDFY24FQ1MTD30-1-5000Fri4Weekday52023
########252SepSeptemberSep-23Sep-23Q320230937########9SeptemberSepPastYTDQTDFY24FQ1MTD30-1-4000Sat5Weekday52023
########253SepSeptemberSep-23Sep-23Q320230937########9SeptemberSepPastYTDQTDFY24FQ1MTD30-1-3000Sun6Weekend52023
Message 8 of 12
1,099 Views
0
skv17
Helper II
Helper II
In response to skv17

‎09-14-2023 10:00 PM

this is the date table

Message 9 of 12
1,099 Views
0
Anonymous
Not applicable
In response to skv17

‎09-17-2023 11:23 PM

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

vxinruzhumsft_0-1695018111402.png

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.

Check if Asset is in a Location between two dates.pbix
Message 10 of 12
1,045 Views
skv17
Helper II
Helper II
In response to Anonymous

‎09-25-2023 08:18 PM

Hey , please help , the gannt chart is not filtering properly

 

Message 12 of 12
982 Views
0
skv17
Helper II
Helper II
In response to Anonymous

‎09-18-2023 04:59 PM

Hi I have put a gant chart but it does not reflect when i select the date, i have attached an image  let me know if you need more infoScreenshot 2023-09-19 075813.png

Message 11 of 12
1,035 Views
0
skv17
Helper II
Helper II

‎09-14-2023 09:58 PM
Ctc_FleetNoLicence PlateFenceNameEntered_EventDateStampEntered_EventTimeStampExited_EventTimeStampSum of Duration in Fence Minutes
PM3261GJL589Depot Hazelmere-Depot110/09/2023 0:0010/09/2023 23:0310/09/2023 23:2522
PM7011HKE056Depot Hazelmere-Depot110/09/2023 0:0010/09/2023 20:5111/09/2023 10:52841
PM3091GJK770Depot Hazelmere-Depot110/09/2023 0:0010/09/2023 20:4711/09/2023 6:57610
PM3091GJK770Depot Hazelmere-Depot110/09/2023 0:0010/09/2023 20:0010/09/2023 20:1212
PM3091GJK770Depot Hazelmere-Depot110/09/2023 0:0010/09/2023 19:1510/09/2023 19:2611
PM3091GJK770Depot Hazelmere-Depot110/09/2023 0:0010/09/2023 18:1310/09/2023 18:207
PM3091GJK770Depot Hazelmere-Depot110/09/2023 0:0010/09/2023 15:4710/09/2023 16:0013
PM3261GJL589Depot Hazelmere-Depot110/09/2023 0:0010/09/2023 14:2910/09/2023 23:02513
PM3261GJL589Depot Hazelmere-Depot110/09/2023 0:0010/09/2023 14:1510/09/2023 14:2813
PM3091GJK770Depot Hazelmere-Depot110/09/2023 0:0010/09/2023 13:2810/09/2023 13:3810
Message 6 of 12
1,099 Views
0
skv17
Helper II
Helper II
In response to skv17

‎09-14-2023 09:59 PM

this is the main table which has the entered and exited event

Message 7 of 12
1,099 Views
0
amitchandak
Super User
Super User

‎09-14-2023 09:24 PM

@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-tr...
Power BI HR Active Employee Tenure Bucketing, and Hired, Terminated, and Active employees: https://youtu.be/fvgcx8QLqZU

 

Power BI Dax Measure- Allocate data between Range: https://youtu.be/O653vwLTUzM

Share with Power BI Enthusiasts: Full Power BI Video (20 Hours) YouTube
Microsoft Fabric Series 60+ Videos YouTube
Microsoft Fabric Hindi End to End YouTube
Message 4 of 12
1,115 Views
skv17
Helper II
Helper II
In response to amitchandak

‎09-17-2023 08:30 PM

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.

Message 5 of 12
1,050 Views
0
Ritaf1983
Super User
Super User

‎09-14-2023 09:19 PM

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

Regards,
Rita Fainshtein | Microsoft MVP
https://www.linkedin.com/in/rita-fainshtein/
Blog : https://www.madeiradata.com/profile/ritaf/profile
Message 2 of 12
1,117 Views
skv17
Helper II
Helper II
In response to Ritaf1983

‎09-15-2023 01:53 AM
tc_FleetNoLicence PlateFenceNameEntered_EventDateStampEntered_EventTimeStampExited_EventTimeStampSum of Duration in Fence Minutes
PM3261GJL589Depot Hazelmere-Depot110/09/2023 0:0010/09/2023 23:0310/09/2023 23:2522
PM7011HKE056Depot Hazelmere-Depot110/09/2023 0:0010/09/2023 20:5111/09/2023 10:52841
PM3091GJK770Depot Hazelmere-Depot110/09/2023 0:0010/09/2023 20:4711/09/2023 6:57610
PM3091GJK770Depot Hazelmere-Depot110/09/2023 0:0010/09/2023 20:0010/09/2023 20:1212
PM3091GJK770Depot Hazelmere-Depot110/09/2023 0:0010/09/2023 19:1510/09/2023 19:2611
PM3091GJK770Depot Hazelmere-Depot110/09/2023 0:0010/09/2023 18:1310/09/2023 18:207
PM3091GJK770Depot Hazelmere-Depot110/09/2023 0:0010/09/2023 15:4710/09/2023 16:0013
PM3261GJL589Depot Hazelmere-Depot110/09/2023 0:0010/09/2023 14:2910/09/2023 23:02513
PM3261GJL589Depot Hazelmere-Depot110/09/2023 0:0010/09/2023 14:1510/09/2023 14:2813
PM3091GJK770Depot Hazelmere-Depot110/09/2023 0:0010/09/2023 13:2810/09/2023 13:3810
Message 3 of 12
1,067 Views
0

Helpful resources

Announcements
Power BI DataViz World Championships

Power BI Dataviz World Championships

The Power BI Data Visualization World Championships is back! Get ahead of the game and start preparing now!

November Power BI Update Carousel

Power BI Monthly Update - November 2025

Check out the November 2025 Power BI update to learn about new features.

FabCon Atlanta 2026 carousel

FabCon Atlanta 2026

Join us at FabCon Atlanta, March 16-20, for the ultimate Fabric, Power BI, AI and SQL community-led event. Save $200 with code FABCOMM.

View All