Forum Discussion

mamoshareef's avatar
mamoshareef
Frequent Visitor
7 years ago
Solved

Calculation the sum in between two dates in two different tables

Hi All I have two tables and there is no relationship between them and I need to calculate the sum of one column in the second table between two specific dates in the first table. Please see the tw...
Need Help
  • mamoshareef's avatar
    mamoshareef
    7 years ago

    thanks a lot, I am editing the first solution to add a new column 

    Spoiler
    Total Fuel Add = 
    CALCULATE(SUM('Table 02'[Fuel Consumption]), FILTER(ALL('Table 02'), 'Table 02'[Work date] >= 'Table 01'[Date Last Stock Date] && 'Table 02'[Work date] <= 'Table 01'Stock take date && 'Table 02'[Water Station]='Table 01'[Water Station] )))
v-yuta-msft's avatar
v-yuta-msft
Community Support
7 years ago

mamoshareef ,

 

You may create a measure using DAX like pattern below:

Total = 
VAR Current_Water_Station = MAX('Table 02'[Water Station])
VAR Stock_Take_Date = CALCULATE(MIN('Table 01'[Stock Take Date]), FILTER('Table 01', 'Table 01'[Water Station] = Current_Water_Station))
VAR The_Fuel_Last_Stock_Take = CALCULATE(MAX('Table 01'[the Fuel Last Stock Take]), FILTER('Table 01', 'Table 01'[Water Station] = Current_Water_Station))
RETURN
CALCULATE(SUM('Table 02'[Fuel Consumption]), FILTER(ALL('Table 02'), 'Table 02'[Work date] >= Stock_Take_Date && 'Table 02'[Work date] <= The_Fuel_Last_Stock_Take))

Community Support Team _ Jimmy Tao

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

mamoshareef's avatar
mamoshareef
Frequent Visitor
7 years ago

Hi,

thanks a lot for your replay but still not working i try to edit but still not work 

 

  • v-yuta-msft's avatar
    v-yuta-msft
    Community Support
    7 years ago

    mamoshareef ,

     

    Could you share the expected result?

     

    Community Support Team _ Jimmy Tao

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.