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: 

Be one of the first to start using Fabric Databases. View on-demand sessions with database experts and the Microsoft product team to learn just how easy it is to get started. Watch now

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
627 Views
0
1 ACCEPTED SOLUTION
v-xinruzhu-msft
Community Support
Community Support
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
534 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
589 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
589 Views
0
v-xinruzhu-msft
Community Support
Community Support
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
535 Views
skv17
Helper II
Helper II
In response to v-xinruzhu-msft

‎09-25-2023 08:18 PM

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

 

Message 12 of 12
472 Views
0
skv17
Helper II
Helper II
In response to v-xinruzhu-msft

‎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
525 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
589 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
589 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

Join us as experts from around the world come together to shape the future of data and AI!
At the Microsoft Analytics Community Conference, global leaders and influential voices are stepping up to share their knowledge and help you master the latest in Microsoft Fabric, Copilot, and Purview. ✨
️ November 12th-14th, 2024
 Online Event
Register Here
Message 4 of 12
605 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
540 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
607 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
557 Views
0

Helpful resources

Announcements
Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!

Dec Fabric Community Survey

We want your feedback!

Your insights matter. That’s why we created a quick survey to learn about your experience finding answers to technical questions.

ArunFabCon

Microsoft Fabric Community Conference 2025

Arun Ulag shares exciting details about the Microsoft Fabric Conference 2025, which will be held in Las Vegas, NV.

December 2024

A Year in Review - December 2024

Find out what content was popular in the Fabric community during 2024.

View All