Calculate daily cost.

[kennyd83]

I'm using a DAX formula to calculate the leasing cost of 5 specific trucks. While the formula works correctly in table and card visuals when paired with the truck_Ids column, it encounters an issue when filtered by a date range.

The problem: If none of the 5 trucks have entries in the data_transport_2023[truck_Ids] within the selected date range, the formula returns no value. This isn't accurate because the leasing cost is a fixed amount per day, regardless of whether the truck was used or not, provided it falls within the contract duration.

Here's my DAX code:

DF_Lease_TotalCost =
VAR DailyCost = 357583,56
VAR LeaseDays = [DF_Lease_DaysCount]
VAR CurrentTruck = SELECTEDVALUE(data_transport_2023[truck_Ids])

VAR TrucksList = { "2732TBU"; "2733TBU"; "2734TBU"; "2735TBU"; "2736TBU" }

VAR TotalCostForListedTrucks =
SUMX(
FILTER(
VALUES(data_transport_2023[truck_Ids]);
data_transport_2023[truck_Ids] IN TrucksList
);
DailyCost * LeaseDays
)

RETURN
IF(
ISINSCOPE(data_transport_2023[truck_Ids]);
IF(
CurrentTruck IN TrucksList;
DailyCost * LeaseDays;
0
);
TotalCostForListedTrucks
)

Can anyone guide me on how to address this issue so that the fixed cost reflects even if a truck has no entries within the filtered date range?

[lbendlin]

To report on things that are not there you need to use disconnected tables and/or crossjoins

Please provide sample data (with sensitive information removed) that covers your issue or question completely, in a usable format (not as a screenshot). Leave out anything not related to the issue.
https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-...
Please show the expected outcome based on the sample data you provided.

https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447...