Measure to calculate lease amount owing for all assets of a particular model for selected date

[RobinHood777]

I am working on a measure to calculate residual lease amount owing for all assets of a particular model for a selected date. My DAX coding is fairly weak so I've tried to refine the table as much as possible in SQL. I've tried many different ways to write the code and keep getting errors.

These are the Tables I have:

'Lease' Table

Asset

Model

LeaseStartDate

LeaseEndDate

LeaseTermMonths (number of months for entire lease)

MonthlyLeaseCost (Monthly cost paid on lease)

TotalCost (total lease value that must be paid)

'Calendar' Table (a Power BI generated calendar table)

Date (all relevant days based on earliest and latest leases)

FirstDay (first day of each month)

Basic idea is to do this for each Asset in the Lease Table:

'Lease'[TotalCost]  – ('Lease'[MonthlyLeaseCost] * DateDiff('Lease'[LeaseStartDate],'Calendar'[FirstDay],MONTH))

Then take the result above and get the sum total for all assets of the selected model, the total amount owing based on a selected day. Allowing you to see how much is owning on a model on a certain date. 'Calendar'[FirstDay] is currently controlled with a single select slicer. Model is controlled with another single select slicer.

Also, the DateDiff output above in months can not be more than LeaseTermMonths as it would indicate the lease is already paid off and I don't want the value to go negative. This complicates it more and I'm not sure how to include this in the code.

[jaweher899]

To calculate the residual lease amount owing for all assets of a particular model for a selected date, you can create a measure using the following DAX code:

ResidualLeaseAmount:= SUMX( FILTER(Lease, Lease[Model] = SELECTEDVALUE(Lease[Model])), MIN(Lease[TotalCost] - (Lease[MonthlyLeaseCost] * DATEDIFF(Lease[LeaseStartDate], SELECTEDVALUE(Calendar[FirstDay]), MONTH))), Lease[LeaseTermMonths] )

[RobinHood777]

ResidualLeaseAmount:= SUMX( FILTER(Lease, Lease[Model] = SELECTEDVALUE(Lease[Model])), MIN(Lease[TotalCost] - (Lease[MonthlyLeaseCost] * DATEDIFF(Lease[LeaseStartDate], SELECTEDVALUE(Calendar[FirstDay]), MONTH))), Lease[LeaseTermMonths] )

It doesn't like the underlined part, gives error "The MIN function only accepts a column reference as an argument", but that is definitely a column in that dataset.

[jaweher899]

please try 

ResidualLeaseAmount:= SUMX(FILTER(Lease, Lease[Model] = SELECTEDVALUE(Lease[Model])), MIN(Lease[TotalCost] - (Lease[MonthlyLeaseCost] * MIN(DATEDIFF(Lease[LeaseStartDate], Calendar[FirstDay], MONTH), Lease[LeaseTermMonths])))

[RobinHood777]

ResidualLeaseAmount:= SUMX(FILTER(Lease, Lease[Model] = SELECTEDVALUE(Lease[Model])), MIN(Lease[TotalCost] - (Lease[MonthlyLeaseCost] * MIN(DATEDIFF(Lease[LeaseStartDate], Calendar[FirstDay], MONTH), Lease[LeaseTermMonths])))

Same error except it highlights this part, this is also definitely a column but it is in a different generated table. If I deleted it to replace, it doesn't even want to auto populate that generated table as a possibility, it only wants to suggest my 2 SQL query tables.

[tamerj1]

Hi @RobinHood777 

Please try

=
SUMX (
'Lease',
'Lease'[TotalCost]
- 'Lease'[MonthlyLeaseCost]
* DATEDIFF ( 'Lease'[LeaseStartDate], MAX ( 'Calendar'[FirstDay] ), MONTH )
)

[RobinHood777]

It doesn't have errors, it might be promising, I think the DateDiff value going above the LeaseTermMonths might be the issue now as the result is negative. How would we make it so if the DateDiff value is > LeaseTermMonths then just use LeaseTermMonths instead?

[tamerj1 · Accepted solution]

@RobinHood777 
Yes you are absolutely right. The selected date could be more than the LeaseEndDate and also could be before the LeaseStartDate. Therefore, we need to limit the calculation by excluding both cases. Please try.

=
VAR SelectedDate =
    MAX ( 'Calendar'[FirstDay] )
VAR T =
    FILTER (
        'Lease',
        'Lease'[LeaseStartDate] < SelectedDate
            && 'Lease'[LeaseEndDate] > SelectedDate
    )
RETURN
    SUMX (
        T,
        'Lease'[MonthlyLeaseCost]
            * DATEDIFF ( SelectedDate, 'Lease'[LeaseEndDate], MONTH )
    ) + 0

The + 0 is optional in case you want to return 0 instead of blank.

[tamerj1]

@RobinHood777 
Have you tried this?

[RobinHood777]

Just tried this, I am getting 0 as the return value.

I really need to do some DAX training as I can't follow this code, but I don't see us still subtracting the cumulative MonthlyLeaseCost from the TotalCost in this.

[tamerj1]

@RobinHood777 

No need to subtract from the total amount. The code multiplies the monthly rent with the number of the left over months. It will return a number only if the selected date is between the start and end dates of the lease contract otherwise it returns 0. The dax should work however, I have suspesions that a relationship is created between the two tables. The dax provided asumes no relationship. 

[RobinHood777]

Ok, I get what you mean, the amount remaining can be determined by using the difference in months rather than getting a sum and subtracting from the total, very smart.

There are no relationships between any tables in this report, I'm not sure why it's returning 0 when it should be around 30K. I get the same result with any model or date selected. My only guess is the filter portion is eliminating eveything for some reason.

[tamerj1]

@RobinHood777 

The slicer from the date table?

[RobinHood777]

It turns out there was some kind of setting issue on the measure I created, when I repasted the code into a new measure it works.