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 DataViz World Championships are on! With four chances to enter, you could win a spot in the LIVE Grand Finale in Las Vegas. Show off your skills.

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
656 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
563 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
618 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
618 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
564 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
501 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
554 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
618 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
618 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

Full Power BI Video 20 Hours YouTube
Microsoft Fabric Series 60+ Videos YouTube
Microsoft Fabric Hindi End to End YouTube
Message 4 of 12
634 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
569 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
636 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
586 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!

FebPBI_Carousel

Power BI Monthly Update - February 2025

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

Feb2025 NL Carousel

Fabric Community Update - February 2025

Find out what's new and trending in the Fabric community.

View All