Calculation the sum in between two dates in two different tables

[mamoshareef]

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 

[Ashish_Mathur]

Hi,

Share the link from where i can download your PBI file.

[mamoshareef]

Hi 
thanks a lot for your support 
please find the link for the report 
https://app.powerbi.com/view?r=eyJrIjoiZGE2NDhkZjYtNDlkOC00ODNiLTk0ZDgtOTMzOGEwN2EyY2RhIiwidCI6IjY5Y...


thanks 

[Ashish_Mathur]

Hi,

I need the link from where i can download the PBI file.  In that file, clearly indicate the source and destination tables.

[mamoshareef · Accepted solution]

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]

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

Hi,

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

[v-yuta-msft]

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