Skip to main content
cancel
Showing results for
Search instead for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Frequent Visitor

## Create measure counting number of available resources at any given time

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

1 ACCEPTED SOLUTION
Super User

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 ) )
)``````
7 REPLIES 7
Community Support

Hi @rubentj ,

Does the above reply help?

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

Best Regards,

Jay

Community Support Team _ Jay
If this post helps, then please consider Accept it as the solution
to help the other members find it.
Super User

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 ) )
)``````
Frequent Visitor

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?

Super User

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 ) )
)``````

Frequent Visitor

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

Super User

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 ) )
)``````
Frequent Visitor

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

## Helpful resources

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

#### Join our Community Sticker Challenge

If you love stickers, then you will definitely want to check out our Community Sticker Challenge!

#### Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

#### Fabric Community Update - July 2024

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

Top Solution Authors
Top Kudoed Authors