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

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Reply
rubentj
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. 

 

CarIdLocationStartDateEndDateNo. of days
1

A

1/1/20201/20/202020
1B1/20/20201/25/20205
1A1/25/202012/31/2020340
2C10/15/202011/30/202046
2A11/30/202012/31/202032

 

 

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
tamerj1
Super User
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 ) )
    )

View solution in original post

7 REPLIES 7
v-jayw-msft
Community Support
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.
tamerj1
Super User
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 ) )
    )

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?

 

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

 

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 

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

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
LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

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

April Fabric Community Update

Fabric Community Update - April 2024

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

Top Solution Authors