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

mgirvin

Well-known Member
Joined
Dec 15, 2005
Messages
1,236
Office Version
  1. 365
Platform
  1. Windows
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
 
Last edited:
Thanks, Matt!! I will try your suggestions.
 
Upvote 0

Excel Facts

Did you know Excel offers Filter by Selection?
Add the AutoFilter icon to the Quick Access Toolbar. Select a cell containing Apple, click AutoFilter, and you will get all rows with Apple
I sent you a private message. Hopefully you will get it : )
 
Upvote 0
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?
 
Upvote 0

Forum statistics

Threads
1,213,546
Messages
6,114,255
Members
448,556
Latest member
peterhess2002

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top