Find the number of days from a large data set.

[uncommon33]

Morning, 

I have a large data set, with a series of entries for each vehcile in the dataset. The dataset only covers a 90 day period. 

How do I count the individual days for each vehicle. In other words, lets say car A has 5,000 entries over the 90 day period. But in that 90 days, that only equates to 10 days. I need to know how to find those 10 days from the 5,000.

I have three date time fields, a mm/dd/yyyy, a hh:mm:ss, and a mm/dd/yyyy hh:mm:ss field. 

I have tried to use calculate with a min and max function, however, I did something wrong with the function.

DAY INSTANCES =

SUMX(

    ADDCOLUMNS(

        DISTINCT('DTE_TBL'[DATE]) ,

        "RESULTS",

        VAR MIN_DATE =

            CALCULATE(MAX(MIN('DTE_TBL'[Date]), SELECTEDVALUE('MSSP DC APPD CLN'[DATE])))

        VAR MAX_DATE =

            CALCULATE(MIN(MAX('DTE_TBL'[Date]), SELECTEDVALUE('MSSP DC APPD CLN'[DATE])))

        RETURN

    ),

    [RESULTS]

)

In the original post I copied this from, the VAR for the MIN and MAX had an opening and closing date. I only have a series of entries for each day the information was recorded.

What I am trying to accomplish is to summarize the total days a given car is in the data set. So if car A is in the data once in 90 days, and car D is in the dataset 45 times in 90 days, the table will show that total.

The second thing I would like to do if possible, is to distinguish between week days and weekend days. So if car A has 10 days in 90, and 5 of those 10 days are on a weekend, then a second column would show the weekend days.

Finally, I would like to be able to filter based on the time. For example, I have location data that correlates with the date and time information. I would like to be able to group the date/time and location so that if a vehicle is in a certain place, it can be summarized accordingly. 

I have put a date table in the dashboard.

Thank you in advance for any assitance.

[uncommon33]

Kind of. Let me add some detail that may help. 

There are 17+ millions records in this dataset. Part of the reason why the set is so big, is because the system records the location data every five minutes with a lat and lon. Therefore in a given day, there are 288 records. If an employee is off and the vehicle is at home, it is still recording the location every five minutes. I need a way to aggregate the location data to show stationary data from moving data. The last measure provides the same total as the DistinctDays measure. 

Is there a way to show movement within this larger dataset?

[rajendraongole1]

I have tried something with operators

create a new calculated column as below and replace

DistanceMoved =

VAR CurrentLat = 'VehicleData'[Latitude]

VAR CurrentLon = 'VehicleData'[Longitude]

VAR PreviousLat =

    CALCULATE(

        MAX('VehicleData'[Latitude]),

        FILTER(

            'VehicleData',

            'VehicleData'[VehicleID] = EARLIER('VehicleData'[VehicleID]) &&

            'VehicleData'[DateTime] = EARLIER('VehicleData'[Datetime]) - 1

        )

    )

VAR PreviousLon =

    CALCULATE(

        MAX('VehicleData'[Longitude]),

        FILTER(

            'VehicleData',

            'VehicleData'[VehicleID] = EARLIER('VehicleData'[VehicleID]) &&

            'VehicleData'[DateTime] = EARLIER('VehicleData'[DateTime]) - 1

        )

    )

VAR R = 6371000 // Earth radius in meters

VAR dLat = RADIANS(CurrentLat - PreviousLat)

VAR dLon = RADIANS(CurrentLon - PreviousLon)

VAR a =

    SIN(dLat / 2) * SIN(dLat / 2) +

    COS(RADIANS(PreviousLat)) * COS(RADIANS(CurrentLat)) *

    SIN(dLon / 2) * SIN(dLon / 2)

VAR c = 2 * ATAN(SQRT(a) / SQRT(1 - a))

VAR Distance = R * c

RETURN

IF(ISBLANK(PreviousLat) || ISBLANK(PreviousLon), BLANK(), Distance)

Hope it works.

[uncommon33]

This is exactly what I was looking for!

One addition/clarification. the location is latitude and longitude corrdinates in two fields. How would I modify the last measure to show the location count by day?

[rajendraongole1]

Hi @uncommon33 - for location as well you can follow the same process

, add a calculated column as like below:

Location = 'VehicleData'[Latitude] & ", " & 'VehicleData'[Longitude]

Create a measure to count the distinct days each vehicle appears at each unique location

DistinctLocationDays =
CALCULATE(
DISTINCTCOUNT('VehicleData'[DateOnly]),
ALLEXCEPT('VehicleData', 'VehicleData'[VehicleID], 'VehicleData'[Location])
)

replace i have used location with LocationLL modify as per your table and column names.

Hope it works

[rajendraongole1]

Hi @uncommon33 - create a calculated column for date only from datetime field of your field.

DateOnly = DATE(YEAR('VehicleData'[DateTime]), MONTH('VehicleData'[DateTime]), DAY('VehicleData'[DateTime]))

Create a measure to count the distinct days each vehicle

DistinctDays =
CALCULATE(
DISTINCTCOUNT('VehicleData'[DateOnly]),
ALLEXCEPT('VehicleData', 'VehicleData'[VehicleID])
)

Create a calculated column to distinguish weekend/weekeday with if condition

DayType =
IF(
WEEKDAY('VehicleData'[DateOnly], 2) <= 5,
"Weekday",
"Weekend"
)

Now lets calcualte measures to count distinct weekdays and weekends

WeekdaysCount =
CALCULATE(
DISTINCTCOUNT('VehicleData'[DateOnly]),
ALLEXCEPT('VehicleData', 'VehicleData'[VehicleID]),
'VehicleData'[DayType] = "Weekday"
)

WeekendsCount =
CALCULATE(
DISTINCTCOUNT('VehicleData'[DateOnly]),
ALLEXCEPT('VehicleData', 'VehicleData'[VehicleID]),
'VehicleData'[DayType] = "Weekend"
)

To get the time create a calculated column for the time part

TimeOnly = TIME(HOUR('VehicleData'[DateTime]), MINUTE('VehicleData'[DateTime]), SECOND('VehicleData'[DateTime]))

Create a measure to count distinct days for each vehicle based on a specified time

DistinctDaysByTimeLocation =
CALCULATE(
DISTINCTCOUNT('VehicleData'[DateOnly]),
ALLEXCEPT('VehicleData', 'VehicleData'[VehicleID]),
'VehicleData'[TimeOnly] >= TIME(8, 0, 0), -- Example start time
'VehicleData'[TimeOnly] <= TIME(17, 0, 0), -- Example end time
'VehicleData'[Location] = "Location1" -- Example location
)

Hope it works to for mentioned scenerios 

[uncommon33]

Very close. The calculation is a total when it's added to the table. 

I have to look at the output to understand where it needs to go. It's very close. It may need a different visual to make sense. I will post again tomorrow once I have a chance to look through this in context.

Thank you for everything so far. I would have never gotten it to this stage.

[v-yiruan-msft]

Hi @uncommon33 ,

May I inquire if your problem has been resolved? Additionally, is there anything else you need assistance with regarding this post?

Best Regards

[uncommon33]

FIrst let me apologize for the very lengthy delay in responding. I have had many projects between this post and the last, along with several weeks of training on unrelated subjects that have prevented me from digging into your solution.

The initial responses are great. I just looked at the last measure in the set for the distance moved. I am fairly confident that I understand the underlying logic and math. However, I don't understand the output. 

I made a modification to the final calculation. 

DistanceMoved =
VAR CurrentLat = 'MSSP DC APPD CLN'[Lat]
VAR CurrentLon = 'MSSP DC APPD CLN'[Lon]
VAR PreviousLat =
CALCULATE(
MAX('MSSP DC APPD CLN'[Lat]),
FILTER(
'MSSP DC APPD CLN',
'MSSP DC APPD CLN'[VehicleID] = EARLIER('MSSP DC APPD CLN'[VehicleID]) &&
'MSSP DC APPD CLN'[DateTime] = EARLIER('MSSP DC APPD CLN'[Datetime]) - 1
)
)
VAR PreviousLon =
CALCULATE(
MAX('MSSP DC APPD CLN'[Lon]),
FILTER(
'MSSP DC APPD CLN',
'MSSP DC APPD CLN'[VehicleID] = EARLIER('MSSP DC APPD CLN'[VehicleID]) &&
'MSSP DC APPD CLN'[DateTime] = EARLIER('MSSP DC APPD CLN'[DateTime]) - 1
)
)
VAR R = 6371000 // Earth radius in meters
VAR dLat = RADIANS(CurrentLat - PreviousLat)
VAR dLon = RADIANS(CurrentLon - PreviousLon)
VAR a =
SIN(dLat / 2) * SIN(dLat / 2) +
COS(RADIANS(PreviousLat)) * COS(RADIANS(CurrentLat)) *
SIN(dLon / 2) * SIN(dLon / 2)
VAR c = 2 * ATAN(SQRT(a) / SQRT(1 - a))
VAR Distance = ROUNDUP((R * c) * 0.0006213712), 2) // THIS SHOULD CONVERT THE DISTANCE FROM METERS TO MILES AND ROUND THE PRODUCT TO 2 DECIMAL PLACES
RETURN
IF(ISBLANK(PreviousLat) || ISBLANK(PreviousLon), BLANK(), Distance)

When this calculation is added to the table as a calculated column, it returns some interesting results no matter how the calculation is entered. It does not seem to clearly interpret the pervious record in the series.

Here's an example of the output.

REC# VEHICLEID DATE TIME DateTime DAY LAT LON Location DistanceMoved ROLLING TIME
1208816 F027 8/12/2023 03:12 PM 08/12/2023 15:12:00 Sat 34.23529 -88.56502 34.23529,-88.56502 10:13
1208910 F027 8/12/2023 03:13 PM 08/12/2023 15:13:00 Sat 34.24639 -88.56842 34.24639,-88.56842 33.19 10:13
1208880 F027 8/12/2023 03:13 PM 08/12/2023 15:13:00 Sat 34.24283 -88.56695 34.24283,-88.56695 33.33 10:13
1208848 F027 8/12/2023 03:13 PM 08/12/2023 15:13:00 Sat 34.23911 -88.56575 34.23911,-88.56575 33.45 10:13

When I enter these coordinates into a mapping program the distance moved is 1 mile. 

Even if remove the roundup parameter from the function, and remove the conversion calculation, I still get the following.

I have made several comparative calculations with other distance calculators. From Google Maps the distance betwee the first and second set of coordinates should be 0.3 mile, according to OMNI calculator, the distance should be .2672  miles or 430 meters. But when I take the rounding functions out, I get 53,831.39. 

I am not sure where the problem is with the calculation. It could the format of the lan and lon, or with the location field which is a combination of the lat and lon fields. 

It also seems to skip records. If the vehicleid and date series are correct, and there are no blanks in either field, the calculation should return a value. There are 36,108 blanks in this calculated filed. 

I would really like this to work and for the most part it does, I just can't use the calculations at the moment.

Thanks for all you help so far.

Again sorry for the delayed response.