topic Re: Create measure counting number of available resources at any given time in DAX Commands and Tips

Create measure counting number of available resources at any given time

rubentj — Sat, 24 Sep 2022 18:40:34 GMT

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

Re: Create measure counting number of available resources at any given time

tamerj1 — Sat, 24 Sep 2022 19:13:39 GMT

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

Re: Create measure counting number of available resources at any given time

rubentj — Sat, 24 Sep 2022 20:04:59 GMT

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

Re: Create measure counting number of available resources at any given time

Anonymous — Wed, 05 Oct 2022 09:55:17 GMT

Hi rubentj ,

Does the above reply help?

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

Best Regards,

Jay

Re: Create measure counting number of available resources at any given time

rubentj — Wed, 26 Oct 2022 11:37:10 GMT

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?

Re: Create measure counting number of available resources at any given time

tamerj1 — Wed, 26 Oct 2022 16:32:42 GMT

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

Re: Create measure counting number of available resources at any given time

rubentj — Wed, 26 Oct 2022 17:02:01 GMT

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

Re: Create measure counting number of available resources at any given time

tamerj1 — Wed, 26 Oct 2022 17:09:15 GMT

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