How to select a applicable value from different table based on given condition

[scoder]

I have two dataset importted in power bi

Dataset1 keeps the appliable loaded amount for evreyday use and this has two dates start and end date which shows from when to when this amount is aplicable

DataSet1

Allotted date	Start Date	End Date	Loaded_Amount	UserId
3/9/2024	3/9/2024	6/26/2024	65853	12
2/28/2024	2/28/2024	3/10/2024	99853	12
1/9/2024	1/09/2024	2/26/2024	77618	12
1/5/2024	1/5/2024	1/27/2024	50972	12
12/28/2023	12/28/2023	1/27/2024	67801	12

Dataset2 has evreyday usage from the customer interms of total_ordered_amount, this has enetry for every day

UserId	date	order amount
12	1/1/2024	100
12	1/2/2024	100
12	1/3/2024	150
12	1/4/2024	170
12	1/5/2024	1000
12	1/6/2024	1050
12	1/7/2024	1300
12	1/8/2024	1040
12	1/9/2024	1600
12	1/10/2024	1900

Now I need to derive the measure which will show daywise loaded amount usage = loaded_amount - total_ordered_amount for each userID 

Here how to get the loaded amount for given date, becuse my dataset1 does not keep daywise amount, it has start_date and end_date

For example ,

case 1: for 1/6/2024 I need to get loaded amount as 50972

Case 2: for 1/10/2024 I need to get as 77618 though the enddate is 1/27/2024 , there is new amount loaded on 1/9/2024 so need to get as 77618 

Is there a some DAX function which can help to calclaute diference of order amount from Dataset1 and Loaded_Amount from Dataset2 based on the lookup with consdiering the above cases 

[Jihwan_Kim · Accepted solution]

Hi,

I am not sure if I understood your question correctly, but I tried to create a sample pbix file like below.

Please check the below picture and the attached pbix file.

Order amount measure: = 
SUM( 'Order'[order amount] )

Load amount measure: =
VAR _currentdate =
    MAX ( 'Calendar'[Date] )
VAR _loadtable =
    FILTER (
        Load,
        Load[Allotted date] <= _currentdate
            && Load[Start Date] <= _currentdate
            && Load[End Date] >= _currentdate
    )
VAR _lastallotteddate =
    MAXX ( _loadtable, Load[Allotted date] )
VAR _laststartdate =
    MAXX ( _loadtable, Load[Start Date] )
RETURN
    SUMX (
        FILTER (
            _loadtable,
            Load[Allotted date] = _lastallotteddate
                && Load[Start Date] = _laststartdate
        ),
        Load[Loaded_Amount]
    )