Forum Discussion

scoder's avatar
scoder
Frequent Visitor
2 years ago
Solved

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

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 dateStart Date   End Date  Loaded_AmountUserId
3/9/20243/9/20246/26/20246585312
2/28/20242/28/20243/10/20249985312
1/9/20241/09/20242/26/20247761812
1/5/20241/5/20241/27/20245097212
12/28/202312/28/20231/27/20246780112

 

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

UserIddateorder amount 
121/1/2024100
121/2/2024100
121/3/2024150
121/4/2024170
121/5/20241000
121/6/20241050
121/7/20241300
121/8/20241040
121/9/20241600
121/10/20241900

 

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's avatar
    Jihwan_Kim
    2 years ago

    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]
    )

     

1 Reply

  • Jihwan_Kim's avatar
    Jihwan_Kim
    Super User
    2 years ago

    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]
    )