Dynamically Determine Rental Status

DAX DAX Calculations
1

Hello All! I’m working on a report where I need to determine the rental status (as 0 or 1) based on the dates in a date slicer. The data consists of a table of objects their On-Rent Date and Off-Rent Date. The goal is to be able to select report historically on an objects rental status based on the date context provided by the slicer.

Attached is a basic report with just the Object No, On-Rent Date and Off-Rent Dates the measure I’ve been working on and two sample tables. The first table which inlcudes the dates returns the correct rental status but the second with only the object number does not.

Any suggestions would be greatly appreciated.
Fleet On Rent.pbix (966.7 KB)

1 Like
2

Hello @mkaess,

Thank You for posting your query onto the Forum.

I’ve a query here pertaining to the scenario that you’ve mentioned. In the first visual, if the “On-Rent” and “Off-Rent” dates fall within the selection of the selected slicer value then they’re yielding the results as either 1 or 0 at an individual item level.

But in the second table, firstly you haven’t provided any date context in the table visual so they’re rendering the incorrect results. Now, the question is, how do you want the results to be showcased at a consolidated level? That is, in the first table visual against “Object No. - 1001-001” there’re line items where results are either evaluated as 0 or 1. So do you want to show the results as 1 even if any one line of the object is evaluated as 1 or do you want to count the number of rows that are being evaluated or yielded as 1 i.e. in this case 5 lines are resulting as 1 and 2 lines are resulting as 0?

Please confirm the scenario.

Thanks and Warm Regards,
Harsh

1 Like
3

Thank you for your reply.

The first table returns the correct results.

To respond to your question

“…how do you want the results to be showcased at a consolidated level? That is, in the first table visual against “ Object No. - 1001-001 ” there’re line items where results are either evaluated as 0 or 1.”

In instances where any row returns a 1, the second table should reflect that as a 1. The goal is that any row from the first if returned as 1 shows that the single object no has been rented within the slicer range.

Cheers - mk

1 Like
4

Hello @mkaess,

Thank You for confirming the scenario.

Below is the measure provided alongwith the screenshot of the final results for the reference -

Fleet On Rent - Harsh = 
VAR _Fleet_On_Rent = 
CALCULATE( COUNTROWS( 'factRental Line' ) , 
    FILTER( VALUES( 'factRental Line'[On-Rent Date] ) , 
        'factRental Line'[On-Rent Date] <= MAX( dimDates[Date] ) ) , 
    FILTER( VALUES( 'factRental Line'[Off-Rent Date] ) , 
        OR( 
            'factRental Line'[Off-Rent Date] >= MIN( dimDates[Date] ) ,
            ISBLANK( 'factRental Line'[Off-Rent Date] ) ) ) )

VAR _Results = 
IF( ISBLANK( _Fleet_On_Rent ) ,
    0 , 
    1 )

RETURN
_Results

Final Results
Final Results1199×768 44 KB

I’m also attaching the working of the PBIX file as well as providing few of the links of the blog posts as well as video links from the EDNA YouTube channel on this topic.

Hoping you find this useful and meets you requirements that you’ve been looking for. :slightly_smiling_face:

Thanks and Warm Regards,
Harsh

Fleet On Rent - Harsh.pbix (967.5 KB)

2 Likes
5

That’s great! Thanks! I’ll update my live report and let you know how it goes. May be early next week before I’m back to this client though. Cheers - mk

1 Like
6

Problem solved! Thank you very much.

1 Like
7

Hello @mkaess,

You’re Welcome. :slightly_smiling_face:

I’m glad that I was able to assist you.

Thanks and Warm Regards,
Harsh

1 Like