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]

I have tried these two formulas, but neither of them work either:

fRentalDates[Number Rentals 03] =
CALCULATE (
COUNTROWS ( dProperties ),
USERELATIONSHIP ( fRentalDates[Actual Move Out Date], dDate[Date] ),
FILTER (
fRentalDates,
fRentalDates[Actual Move Out Date] >= MAX ( dDate[Date] )
&& fRentalDates[Actual Rent Date] <= MIN ( dDate[Date] )
)
)

and

fRentalDates[Number Rentals 04] =
CALCULATE (
CALCULATE (
COUNTROWS ( dProperties ),
FILTER ( fRentalDates, fRentalDates[Actual Rent Date] <= MIN ( dDate[Date] ) )
),
USERELATIONSHIP ( fRentalDates[Actual Move Out Date], dDate[Date] ),
FILTER (
fRentalDates,
fRentalDates[Actual Move Out Date] >= MAX ( dDate[Date] )
)
)

What is so frustrating for me, is I created an Excel spreadhseet formula in 5 minutes to make this calculation, but after 6 hours of trying to create that same type of formula in DAX, I have had no success.

The Excel Formula is:

=SUMPRODUCT(
--(
COUNTIFS(fRentalDates[Property Number],dProperties[Property Number],
fRentalDates[Actual Rent Date],"<="&Start_Date,
fRentalDates[Actual Move Out Date],">="&End_Date)
)>0
)

Sincerely, Mike Girvin

 

[mgirvin]

Yet another failed attempt, this time using DAX Studio:

DEFINE MEASURE
fRentalDates[NumRentals] =
VAR StartDate = MIN(dDate[Date])
VAR EndDate = MAX(dDate[Date])
RETURN
COUNTROWS(
CALCULATETABLE(
dProperties,
fRentalDates[Actual Rent Date]<=StartDate ,
USERELATIONSHIP(fRentalDates[Actual Move Out Date],
dDate[Date]),fRentalDates[Actual Move Out Date]>=EndDate
)
)
EVALUATE
ADDCOLUMNS(
VALUES(dDate[Month Year]),
"Number of Rentals",
[NumRentals]
)

Every row in the table gets a count of 20.

 

[mgirvin]

Okay. I figured it out. I was clouding my thinking because I was stuck in between Excel Spreadsheet Formula logic and DAX Formula logic. Not only did I not figure out the DAX yesterday, but the Excel formula I created was simulating "Table Functions" in DAX and was much more complicated that it needed to be. The formulas that I got working today are:

Excel: =COUNTIFS(fRentalDates[Actual Rent Date],"<="&V2,fRentalDates[Actual Move Out Date],">="&W2)

DAX: =COUNTROWS(FILTER(ALL(fRentalDates),fRentalDates[Actual Rent Date]<=MIN(dDate[Date]) && fRentalDates[Actual Move Out Date]>=MAX(dDate[Date])))

 

[Matt Allington]

Hi Mike

glad you worked it out. The problem is very similar to the one I covered here https://powerpivotpro.com/2016/12/how-many-working-days-has-an-employee-been-off-work/

you are of course highlighting what many Excel pros discover. That is that power pivot takes a different thinking style to Excel. I can tell you it gets easier with time. My best advice is "think filter first, then calculate)

 

[mgirvin]

Thanks, Matt!
I have been using DAX for at least a few years and usually do think "Filter First", but yesterday I got stuck between "Excel Spreadsheet Formula" thinking and "DAX Data Model" thinking, and... first made the mistake with my Excel formula and then I just could not figure out the DAX formula. The crazy thing is, I have done this exact DAX type formula before, but I got stuck and spent too many hours with no solution. The funny thing and good news was, that I woke up in the middle of the night and remembered ALL. it was 3:30 AM and I was tempted to run downstairs to my computer and write the DAX Measure, but I went back to sleep and then when I woke up at 6:30 AM I just went and wrote the formula with ALL... As you say, it is quite a transition and struggle to go from Excel Spreadsheet Formula logic to DAX logic.
Thanks for the comment and support : )

 

[mgirvin]

Matt,

I have a slightly different situation for counting number of units rented.

I have 3 tables:

1) dDate with Date, Month and Year
2) fRentalDates (Fact Table) with RentStartDate, RentStopDate and NameKey
3) dName with NameKey and Name

The Fact Table has a relationship between Date column in dDate table and NameKey in dName Table.

I have built a PivotTable with Year and Month from the dDate Table. And I have a slicer for Name coming from the dName Table.

I am trying to count the number of rented units each month.

The RentStopDate has data values and blanks.

I have dropped this Measure into the Report:

# Rented 01 = AVERAGEX(VALUES(dDate[Date]),CALCULATE(COUNTROWS(FILTER(ALL(fRentalDates),fRentalDates[RentStartDate]<=MIN(dDate[Date]) && OR(fRentalDates[RentStopDate]=MAX(dDate[Date]),ISBLANK(fRentalDates[RentStopDate]))))))

When I make a selection on the Name Slicer, the Measure does not reflect that selection.

Any ideas?

 

[mgirvin]

I am trying to post the formatted formula:

fRentalDates[# Rented 01] =
CALCULATE (
AVERAGEX (
VALUES ( dDate[Date] ),
CALCULATE (
COUNTROWS (
FILTER (
ALL ( fRentalDates ),
fRentalDates[RentStartDate] <= MIN ( dDate[Date] )
&& OR (
fRentalDates[RentStopDate] = MAX ( dDate[Date] ),
ISBLANK ( fRentalDates[RentStopDate] )
)
)
)
)
),
VALUES ( dName[Name] )
)

 

[mgirvin]

that didn't work...

 

[Matt Allington]

Mike

i haven't looked in depth at your formula, but is seems to me that the relationship you have on the start date may be not needed. This relationship will always filter the data to only include Rentals that started that month (although your all function removes it). I would remove the relationship and the ALL().

The rest of your formula looks directionally ok, but maybe you need >= max(date[date])

This measure should return the average daily rental for the selected period