Solved: Re: Check if Asset is in a Location between two da...

skv17 · ‎09-14-2023

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

v-xinruzhu-msft · ‎09-17-2023

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.

View solution in original post

skv17 · ‎09-14-2023

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

Q3

202309

37

########

9

September

Sep

Past

YTD

QTD

FY24

FQ1

MTD

3

0

-1

-9

0

Mon

0

Weekend

5

2023

########

248

Sep

September

Sep-23

Q3

202309

37

########

9

September

Sep

Past

YTD

QTD

FY24

FQ1

MTD

3

0

-1

-8

0

Tue

1

Weekday

5

2023

########

249

Sep

September

Sep-23

Q3

202309

37

########

9

September

Sep

Past

YTD

QTD

FY24

FQ1

MTD

3

0

-1

-7

0

Wed

2

Weekday

5

2023

########

250

Sep

September

Sep-23

Q3

202309

37

########

9

September

Sep

Past

YTD

QTD

FY24

FQ1

MTD

3

0

-1

-6

0

Thu

3

Weekday

5

2023

########

251

Sep

September

Sep-23

Q3

202309

37

########

9

September

Sep

Past

YTD

QTD

FY24

FQ1

MTD

3

0

-1

-5

0

Fri

4

Weekday

5

2023

########

252

Sep

September

Sep-23

Q3

202309

37

########

9

September

Sep

Past

YTD

QTD

FY24

FQ1

MTD

3

0

-1

-4

0

Sat

5

Weekday

5

2023

########

253

Sep

September

Sep-23

Q3

202309

37

########

9

September

Sep

Past

YTD

QTD

FY24

FQ1

MTD

3

0

-1

-3

0

Sun

6

Weekend

5

2023

skv17 · ‎09-14-2023

this is the date table

v-xinruzhu-msft · ‎09-17-2023

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.

skv17 · ‎09-25-2023

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

skv17 · ‎09-18-2023

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 info

skv17 · ‎09-14-2023

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 · ‎09-14-2023

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

amitchandak · ‎09-14-2023

@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

!! Power BI 101 Interview questions !! !! Master Microsoft Fabric- 36 Videos !!
Microsoft Power BI Learning Resources, 2023 !!
Learn Power BI - Full Course with Dec-2022, with Window, Index, Offset, 100+ Topics !!
Did I answer your question? Mark my post as a solution! Appreciate your Kudos !! Proud to be a Super User! !!

skv17 · ‎09-17-2023

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.

Ritaf1983 · ‎09-14-2023

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

skv17 · ‎09-15-2023

tc_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

Check if Asset is in a Location between two dates

Helpful resources

Europe’s largest Microsoft Fabric Community Conference

Power BI Monthly Update - August 2024

Fabric Community Update - August 2024

How to Get Your Question Answered Quickly

European Microsoft Fabric Community Conference

Check if Asset is in a Location between two dates

Helpful resources

Europe’s largest Microsoft Fabric Community Conference

Power BI Monthly Update - August 2024

Fabric Community Update - August 2024

How to Get Your Question Answered Quickly