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 two tables I have.

The specific need is the sum process colored in red. That means, the sum of values in the second table between the dated in table one ( column: Stock Take Date and column: Date Last Stock Take) for the same water station. 

Please note that the column (Date Last Stock Date) is claculated through the following equation:
CALCULATE(
MAX(table01[Stock take date]),
FILTER(
ALLEXCEPT(table01,table01[Water_Station]),
table01[Stock take date]<EARLIER(table01[Stock take date])))
 In addition, the column (the Fuel Last Stock Take) is calcuated through the followng equation:
LOOKUPVALUE(table01[Total Fuel Amount in Water Station],table01[Stock take date],table01[Date Last Stock Date],table01[Water Station],table01[Water Station])
 
Thanks in advance 
 

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

7 Replies

  • 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.