DAX Measure that will count the number of properties rented each month

[mgirvin]

Dear Team,

My goal is to find a DAX Measure that will count the number of properties rented each month.

My Data Model consists of three tables. The tables are:

1) fRentalDates, with the field names: Transaction Number, Property Number, Rent Order Date, Actual Rent Date, Move Out Order Date, Actual Move Out Date

2) dProperties, with field names: Property Number, Address, Date Purchased, Date Sold, SQ FT

3) dDate, with field names: Date, Day, MonthNumber, MonthName, Month Year, Year

There is a One-To-Many relationship between dProperties[Property Number] and fRentalDates[Property Number]. And a second One-To-Many relationship between dDate[Date] and fRentalDates[Rent Order Date].

My first question is: I was not sure which date column in the fRentalDates I should create a relationship with. I chose the fRentalDates[Rent Order Date] column. If that is not a good column, maybe someone knows the correct column for the relationship?

For the DAX Measure that will count the number of properties rented each month, I dropped the Month Year field into the PivotTable Row Area, and then I tried these two Measures:

Number Rented 01 =

COUNTX (

dProperties,

CALCULATE (

COUNTROWS (

FILTER (

fRentalDates,

AND (

fRentalDates[Actual Rent Date] <= MIN ( dDate[Date] ),

fRentalDates[Actual Move Out Date] >= MAX ( dDate[Date] )

)

)

)

)

)

and then I tried:

=

COUNTX (

dProperties,

COUNTROWS (

FILTER (

fRentalDates,

AND (

fRentalDates[Actual Rent Date] <= MIN ( dDate[Date] ),

fRentalDates[Actual Move Out Date] >= MAX ( dDate[Date] )

)

)

)

)

Neither one gave me the correct answer.

Does anyone know how to make a DAX Formula that can accomplish the goal of counting the number of properties rented each month?

If you want to download the file I am using, here it is:

https://people.highline.edu/mgirvin/YouTubeExcelIsFun/BIDeskrtopRentalTest04.xlsx

BTW, I have accomplished all the formulas I need using Excel Spreadsheet formula. You can see these on the sheet "Tables". These can be check numbers for the correct result for the formula "Number Properties Rented". I just need to now figure how to do it in DAX.

Sincerely, Mike Girvin

 

[mgirvin]

Thanks, Matt!! I will try your suggestions.

 

[mgirvin]

I sent you a private message. Hopefully you will get it : )

 

[mgirvin]

Thanks very much, Matt: for the "remove the relationship and the ALL()" tip. It helped : )

Here is the formula as it stands now, and it appears to be giving us the correct answer:

# Rentals =
AVERAGEX (
VALUES ( dDate[Date] ),
CALCULATE (
COUNTROWS (
FILTER (
fRentalDates,
AND (
fRentalDates[era_RentStartDate] <= MIN ( dDate[Date] ),
NOT ( ISBLANK ( fRentalDates[era_RentStartDate] ) )
)
&& OR (
fRentalDates[era_RentStopDate] = MAX ( dDate[Date] ),
ISBLANK ( fRentalDates[era_RentStopDate] )
)
)
)
)
)

I have tried this alternative formula, which I thought would give the equivalent answer:

# Rentals CALC =
VAR MinDate =
MIN ( dDate[Date] )
VAR MaxDate =
MAX ( dDate[Date] )
RETURN
AVERAGEX (
VALUES ( dDate[Date] ),
CALCULATE (
COUNTROWS ( fRentalDates ),
fRentalDates[era_RentStartDate] <= MinDate,
NOT ( ISBLANK ( fRentalDates[era_RentStartDate] ) ),
fRentalDates[era_RentStopDate]=MaxDate
||
ISBLANK(fRentalDates[era_RentStopDate])
)
)

But they give slightly different answers. Shouldn't these formulas yield the same answer? Are these formulas logically different, or is the DAX engine evaluating them in a different way?