Rental Object Utilisation Ration

DAX DAX Calculations
1

Greetings All! I’m trying to build a measure that can be sued in various visuals to show the utilisation of rental items (objects) by object or grouped. In this calculation there are four dates that are used:

  • Min Date in Context (earliest slicer date)
  • Max Date in Context (latest slicer date)
  • On-Rent Date (Date the object rental started)
  • Expiration Date (Date object rental ended)
    The goal is for dynamic visuals based on the dates selected in the slicer and the ability to show current month, last month, etc… I have built the key measures to use in the final one but when using object group in the visual, the results are blank. If I do it in a table by the object itself, I get the values desired.

The calculation for utilisation is this

(SUM of the number of days units are rented) / (Number of Objects x Number of possible rental days)

I have built a pbix file with redacted data from the model I’m using and the measures I’ve created. Hope that helps. Thanks in advance. Rental Object Analysis.pbix (1.7 MB)

2

Hi @mkaess,

So a couple of observations, your measure:

Object Days On-Rent = 
VAR OnRentDate =
    CALCULATE (
        VALUE ( SELECTEDVALUE ( 'Rental Contract Line'[On-Rent Date] ) ),
        CROSSFILTER( Dates[Date], 'Rental Contract Line'[On-Rent Date], Both )
    )
VAR ExpiryDate =
    CALCULATE (
        VALUE ( SELECTEDVALUE ( 'Rental Contract Line'[Expiration Date] ) ),
        CROSSFILTER( Dates[Date], 'Rental Contract Line'[Expiration Date], Both )
    )
VAR MinDateInContext =
    VALUE ( MIN ( Dates[Date] ) )
VAR MaxDateInContext =
    VALUE ( MAX ( Dates[Date] ) )
RETURN
    IF (
        AND ( OnRentDate < MinDateInContext, ExpiryDate > MaxDateInContext ),
        MIN( ExpiryDate, MaxDateInContext ) - MIN( OnRentDate, MinDateInContext ),
        IF (
            AND ( OnRentDate > MinDateInContext, ExpiryDate < MaxDateInContext ),
            ExpiryDate - MinDateInContext,
            BLANK()
        )
    )

.

Could also be writen as:

Object Days On-Rent SELECTEDVALUE = 
VAR OnRentDate = SELECTEDVALUE ( 'Rental Contract Line'[On-Rent Date] )
VAR ExpiryDate = SELECTEDVALUE ( 'Rental Contract Line'[Expiration Date] )
VAR MinDateInContext = MIN ( Dates[Date] )
VAR MaxDateInContext = MAX ( Dates[Date] )
RETURN
    IF (
        AND ( OnRentDate < MinDateInContext, ExpiryDate > MaxDateInContext ),
        MIN( ExpiryDate, MaxDateInContext ) - MIN( OnRentDate, MinDateInContext ),
        IF (
            AND ( OnRentDate > MinDateInContext, ExpiryDate < MaxDateInContext ),
            ExpiryDate - MinDateInContext,
            BLANK()
        )
    )

That produces the same result of course…
.

To bring in the context from the Object No try iterating over a virtual table, first attempt.

Object Days On-Rent v2 = 
VAR MinDateInContext = MIN ( Dates[Date] )
VAR MaxDateInContext = MAX ( Dates[Date] )
VAR vTable =
    FILTER(
        ADDCOLUMNS( 'Rental Contract Line',
            "@DaysOnRent", 
                IF (
                    AND ( [On-Rent Date] < MinDateInContext, [Expiration Date] > MaxDateInContext ),
                    MIN( [Expiration Date], MaxDateInContext ) - MIN( [On-Rent Date], MinDateInContext ),
                    IF (
                        AND ( [On-Rent Date] > MinDateInContext, [Expiration Date] < MaxDateInContext ),
                        VALUE( [Expiration Date]  ) - VALUE( MinDateInContext ),
                        BLANK()
                    )
                )
        ), [@DaysOnRent] <> BLANK()
    )
RETURN

SUMX( vTable, [@DaysOnRent] )

However these results don’t match yours…

image

Investigated a subset a little closer, see the marked values above and below …

image
image1286×248 34.3 KB

Looks like the only difference is that the [Expiration Date] is equal to the last date in your Date table…
To check if that is the case for all results, amended the measure.

Object Days On-Rent v3 = 
VAR MinDateInContext = MIN ( Dates[Date] )
VAR MaxDateInContext = MAX ( Dates[Date] )
VAR LastCalendarDate = CALCULATE( MAX( Dates[Date] ), ALL(Dates[Date] ))
VAR vTable =
    FILTER(
        ADDCOLUMNS( 'Rental Contract Line',
            "@DaysOnRent", 
                IF (
                    AND ( [On-Rent Date] < MinDateInContext, [Expiration Date] > MaxDateInContext ),
                    MIN( [Expiration Date], MaxDateInContext ) - MIN( [On-Rent Date], MinDateInContext ),
                    IF (
                        AND ( [On-Rent Date] > MinDateInContext, [Expiration Date] < MaxDateInContext ),
                        VALUE( [Expiration Date]  ) - VALUE( MinDateInContext ),
                        BLANK()
                    )
                )
        ), [@DaysOnRent] <> BLANK() 
        && [Expiration Date] = LastCalendarDate
    )
RETURN

SUMX( vTable, [@DaysOnRent] )

.

and this matches your results. The question now becomes, is that correct??

image
image694×641 22.1 KB

I hope this is helpful.

3 Likes
3

@Melissa - Thanks for the reply. My original measure was a take off of. Occupancy video as it was the closest to what I was trying to achieve. I’ll take a closer look at post the results.

4

Hi @mkaess, did the response provided by the contributors help you solve your query? If not, how far did you get and what kind of help you need further? If yes, kindly mark the thread as solved. Thanks!

5

Amazing support here!

6

Very amazing indeed!

7

Hi @Melissa with some more trial and error (and your help), the calcuation is working great! Thanks again.

Object Days On-Rent v4 =
VAR MinDateInContext =
MIN ( Dates[Date] )
VAR MaxDateInContext =
MAX ( Dates[Date] )
VAR vTable =
FILTER (
ADDCOLUMNS (
‘Object Ledger Entry’,
“@DaysOnRent”,
IF (
AND (
[Rental From Date] <= MinDateInContext,
[Rental To Date] >= MaxDateInContext
),
DATEDIFF ( MinDateInContext, MaxDateInContext, DAY ) + 1,
IF (
AND (
[Rental From Date] <= MinDateInContext,
[Rental To Date] <= MaxDateInContext
),
DATEDIFF ( VALUE ( MinDateInContext ), VALUE ( [Rental To Date] ), DAY ) + 1,
IF (
AND (
[Rental From Date] >= MinDateInContext,
[Rental To Date] >= MaxDateInContext
),
DATEDIFF ( VALUE ( [Rental From Date] ), VALUE ( MaxDateInContext ), DAY ) + 1,
IF (
AND (
[Rental From Date] >= MinDateInContext,
[Rental To Date] <= MaxDateInContext
),
DATEDIFF ( VALUE ( [Rental From Date] ), VALUE ( [Rental To Date] ), DAY ) + 1,
BLANK ()
)
)
)
)
),
[@DaysOnRent] <> BLANK ()
&& ‘Object Ledger Entry’[Entry Category] = “Ext Rental Income”
)
RETURN
SUMX ( vTable, [@DaysOnRent] )