Create measure counting number of available resources at any given time

[rubentj]

Hi guys. 

So, I have a table with information of cars location of any given time, where car movements are gives me a new row. 

CarId	Location	StartDate	EndDate	No. of days
1	A	1/1/2020	1/20/2020	20
1	B	1/20/2020	1/25/2020	5
1	A	1/25/2020	12/31/2020	340
2	C	10/15/2020	11/30/2020	46
2	A	11/30/2020	12/31/2020	32

What I want to calculate is the number of "Car days" available in various periods (per day, per month, per quarter etc). For example, I want the measure to say that at 1/1/2020 there were 1 Car day (since only Car 1 was available on Location A - Car 2 didnt "exist" at that point in time). Further, in January 2020 on Location A, there were 20 (row A) + 6 (row 3 - this location data is calculated by StartDate being 1/25/2020 but end of month is 1/31/2020) = 26 available car days.

So, the issue I have is creating the logic where based on Min and Max dates i sum the number of days for each row, given the period I look at. If I evaluate per month basis, the Min(Date) is the 1st of the month and the Max(date) is the last day of the month. 

I do of course have a standard Date table. 

Thanks,

Ruben

[v-jayw-msft]

Hi @rubentj ,

Does the above reply help?

If the problem is resolved, please mark the reply as solution.

Best Regards,

Jay

[tamerj1 · Accepted solution]

Hi @rubentj 

I didn't test to be honest, but please try

Mumber of Days =
VAR MinDate =
    MIN ( 'Date'[Date] )
VAR MaxDate =
    MAX ( 'Date'[Date] )
VAR Dates1 =
    CALENDAR ( MinDate, MaxDate )
VAR Cars =
    CALCULATETABLE (
        'Cars Data',
        CROSSFILTER ( 'Cars Data'[StartDate], 'Date'[Date], NONE )
    ) -- in case no relationship just use 'Cars Data'
RETURN
    SUMX (
        Cars,
        VAR Dates2 =
            CALENDAR ( 'Cars Data'[StartDate], 'Cars Data'[EndtDate] )
        RETURN
            COUNTROWS ( INTERSECT ( Dates1, Dates2 ) )
    )

[rubentj]

Hi again @tamerj1.

So the solution worked as per my requirement. 

However, I have come up with another thing I need to adjust for. 

Now I count the car two times on the dates which have start and end dates. More specifically, if a Car has end date 31 Dec 2020, another entry has the start date 31 Dec 2020, meaning that I am double counting the cars. 

I tried doing an easy fix to this by setting the end date to the date before, but this gives me the problem of the end date being previous to the start date, and this gives me an error. 

Do you have any suggestions?

[tamerj1]

Hi @rubentj 

have you tried to minus one from EndDate?

Mumber of Days =
VAR MinDate =
    MIN ( 'Date'[Date] )
VAR MaxDate =
    MAX ( 'Date'[Date] )
VAR Dates1 =
    CALENDAR ( MinDate, MaxDate )
VAR Cars =
    CALCULATETABLE (
        'Cars Data',
        CROSSFILTER ( 'Cars Data'[StartDate], 'Date'[Date], NONE )
    ) -- in case no relationship just use 'Cars Data'
RETURN
    SUMX (
        Cars,
        VAR Dates2 =
            CALENDAR ( 'Cars Data'[StartDate], 'Cars Data'[EndtDate] - 1 )
        RETURN
            COUNTROWS ( INTERSECT ( Dates1, Dates2 ) )
    )

[rubentj]

Yes, but the problem is that in some cases, the start and end date is at the same date, but different time stamps. That gives me an error in the calendar function. Is it possible to exclude those cases from the calculation? 

In addition, I have a timestamp in a column - is it possible to say that "if the start time stamp is after 12:00AM, use the following date" && "if end date is before 12:00AM, use the previous date" 

Thank you so much for your help @tamerj1 

[tamerj1]

@rubentj 

Please try

VAR Mumber of Days =
VAR MinDate =
    MIN ( 'Date'[Date] )
VAR MaxDate =
    MAX ( 'Date'[Date] )
VAR Dates1 =
    CALENDAR ( MinDate, MaxDate )
VAR Cars =
    CALCULATETABLE (
        'Cars Data',
        CROSSFILTER ( 'Cars Data'[StartDate], 'Date'[Date], NONE )
    ) -- in case no relationship just use 'Cars Data'
RETURN
    SUMX (
        Cars,
        VAR Dates2 =
            CALENDAR (
                'Cars Data'[StartDate],
                MAX ( 'Cars Data'[StartDate], 'Cars Data'[EndtDate] - 1 )
            )
        RETURN
            COUNTROWS ( INTERSECT ( Dates1, Dates2 ) )
    )

[rubentj]

Thank you so much, this is art!!! - it looks very good, and at a first glance, it seems to work. Will do some reconsiliation.