Why Two "Count Rentals" Measures Yield Different Answers?

[mgirvin]

Dear Team,

I have two Measures that I expect to yield the same answer and I am wondering if anyone can see why they are not yielding the same answer.

The goal of both formulas is to count the number of Rented Units based on a Start Date and Stop Date.

In the Data Model I have 4 tables:

1) fRentDateTransactions with columns for Start Date and Stop Date
2) dApartmentList with a unique list of apartments that is related to fRentDateTransactions
3) dCommunityAccount with a unique list of communities that is related to dApartmentList
4) dDate that is the Date table and is not related to any of the tables

I have a table with Year, Month and Day (from the dDate Table) on the rows and I have the two "count the number of Rented Units based on a Start Date and Stop Date" DAX Measures calculating for each Year, Month and Day.

The two formulas are:

# Rentals =
AVERAGEX (
VALUES ( dDate[Date] ),
CALCULATE (
COUNTROWS (
FILTER (
fRentDateTransactions,
AND (
fRentDateTransactions[RentStartDate] <= MIN ( dDate[Date] ),
NOT ( ISBLANK ( fRentDateTransactions[RentStartDate] ) )
)
&& OR (
fRentDateTransactions[RentStopDate] = MAX ( dDate[Date] ),
ISBLANK ( fRentDateTransactions[RentStopDate] )
)
)
)
)
)

and

# Rentals CALC =
VAR StartDate = MIN(dDate[Date])
VAR EndDate = MAX(dDate[Date])
VAR StartDateValidListTable =
FILTER(fRentDateTransactions,
AND(fRentDateTransactions[RentStartDate]<=StartDate,
NOT(ISBLANK(fRentDateTransactions[RentStartDate]))))
VAR EndDateValidListTable =
FILTER(fRentDateTransactions,
OR(fRentDateTransactions[RentStopDate]=EndDate,
ISBLANK(fRentDateTransactions[RentStopDate])))
RETURN
AVERAGEX (
VALUES ( dDate[Date] ),
CALCULATE(
COUNTROWS(fRentDateTransactions),
StartDateValidListTable,
EndDateValidListTable
)
)

Any ideas?

Also, anyone know how I can get DAX Measure to post with all the indents?

 

[gazpage]

Use the # in the formatting tools to get the indenting.

Just to check, but you realise that Variables are calculated when they are defined and not when they are used, right? In the second example the Start and EndDate don't change with iterations of AVERAGES().

 

[mgirvin]

Thanks for your post, gazpage!!!

I do not know what you mean by "Use the # in the formatting tools to get the indenting." Can you explain more?

 

[mgirvin]

gazpage,

Yes, I know that VAR calculate where they are defined. But to me, for these two formulas I see the same result for the VARs and MIN and MAX calculations as the same in either case, maybe I am wrong though.

When I look at the two formulas, I see that the Filter Context From the row area of the report flow into both formulas and yield the same numbers for the VAR. I see it this way:

Formula # [1]:
# Rentals =
AVERAGEX (
VALUES ( dDate[Date] ),
CALCULATE (
COUNTROWS (
FILTER (
fRentDateTransactions,
AND (
fRentDateTransactions[RentStartDate] <= MIN ( dDate[Date] ),
NOT ( ISBLANK ( fRentDateTransactions[RentStartDate] ) )
)
&& OR (
fRentDateTransactions[RentStopDate] = MAX ( dDate[Date] ),
ISBLANK ( fRentDateTransactions[RentStopDate] )
)
)
)
)
)
Formula #[2]:
# Rentals CALC =
VAR StartDate = MIN(dDate[Date])
VAR EndDate = MAX(dDate[Date])
VAR StartDateValidListTable =
FILTER(fRentDateTransactions,
AND(fRentDateTransactions[RentStartDate]<=StartDate,
NOT(ISBLANK(fRentDateTransactions[RentStartDate]))))
VAR EndDateValidListTable =
FILTER(fRentDateTransactions,
OR(fRentDateTransactions[RentStopDate]=EndDate,
ISBLANK(fRentDateTransactions[RentStopDate])))
RETURN
AVERAGEX (
VALUES ( dDate[Date] ),
CALCULATE(
COUNTROWS(fRentDateTransactions),
StartDateValidListTable,
EndDateValidListTable
)
)

In [1] the Filter Context From the row area of the report flows into VALUES( to define a list of days, which could be at the day, month or year) and then that flows into MIN and MAX. so MIN and MAX will pick out minimum and maximum dates from Filter Context From the row area of the report for each row in the report. That is, it will pick out the minimum and maximum dates from the Filter Context of the dDate Date Table. Note: there is no relationship between Date and Fact table.

In [2] the VARs are up front and so they see the Filter Context From the row area of the report right away and should calculate the same dates as [1].

Am I way off base in my thinking?

 

[mgirvin]

Also, when I move the VARS into AVERAGEX, I get the same answers. These two formulas yield the same answer:

# [1]:
AVERAGEX(
VALUES(dDate[Date]),
VAR StartDate = MIN(dDate[Date])
VAR EndDate = MAX (dDate[Date])
VAR RentDatesBeforeMin =
FILTER(
fRentDates,
AND(
fRentDates[RentStartDate] <= StartDate,
NOT (ISBLANK(fRentDates[RentStartDate]))
)
)
VAR RentDatesEqualMax =
FILTER(
fRentDates,
OR(
fRentDates[RentStopDate] = MAX(dDate[Date]),
ISBLANK(fRentDates[RentStopDate])
)
)
RETURN
CALCULATE(
COUNTROWS(fRentDates),
RentDatesBeforeMin,
RentDatesEqualMax,
ALL(dDate)
)
)

# [2]:
VAR StartDate = MIN(dDate[Date])
VAR EndDate = MAX(dDate[Date])
VAR StartDateValidListTable =
FILTER(fRentDateTransactions,
AND(fRentDateTransactions[RentStartDate]<=StartDate,
NOT(ISBLANK(fRentDateTransactions[RentStartDate]))))
VAR EndDateValidListTable =
FILTER(fRentDateTransactions,
OR(fRentDateTransactions[RentStopDate]=EndDate,
ISBLANK(fRentDateTransactions[RentStopDate])))
RETURN
AVERAGEX (
VALUES ( dDate[Date] ),
CALCULATE(
COUNTROWS(fRentDateTransactions),
StartDateValidListTable,
EndDateValidListTable
)
)

 

[gazpage]

Thanks for your post, gazpage!!!

I do not know what you mean by "Use the # in the formatting tools to get the indenting." Can you explain more?

When you are typing a post, up where the Bold, Italics etc are, there is a #. If you can't see it then just use

around your code.

 

[gazpage]

Also, when I move the VARS into AVERAGEX, I get the same answers. These two formulas yield the same answer:

Well, indeed. That was what I would have expected. Let's assume your filter context is the year 2016 and you have daily dates in the Date column.

In [2], the Start Date and End Date are calculated up front so are 1 January 2016 and 31 December 2016.
In [1] your AVERAGEX is iterating over the dates. So first it does to 1 January 2016 and then the second clause in the AVERAGEX is calculated in that context. Therefore the MIN and MAX are 1 January 2016 and 1 January 2016.

By moving the VARs inside the AVERAGEX in [2], they are calculated in the same way as [1].

 

[mgirvin]

Alright, cool : ) I am going to try the # thing:

[# Rentals] =
AVERAGEX (
    VALUES ( dDate[Date] ),
    CALCULATE (
        COUNTROWS (
            FILTER (
                fRentDateTransactions,
                AND (
                    fRentDateTransactions[RentStartDate] <= MIN ( dDate[Date] ),
                    NOT ( ISBLANK ( fRentDateTransactions[RentStartDate] ) )
                )
                    && OR (
                        fRentDateTransactions[RentStopDate] = MAX ( dDate[Date] ),
                        ISBLANK ( fRentDateTransactions[RentStopDate] )
                    )
            )
        )
    )
)

 

[mgirvin]

That is so easy and so cool : ) Thanks gazpage!!!

 

[mgirvin]

How about my thinking in post #4 ? Is it correct, or am I off?