cancel
Showing results for
Did you mean:
Highlighted
Frequent Visitor

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

1 ACCEPTED SOLUTION

Accepted Solutions
Frequent Visitor

## Re: Calculation the sum in between two dates in two different tables

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

Spoiler
`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 7
Community Support Team

## Re: Calculation the sum in between two dates in two different tables

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.

Frequent Visitor

## Re: Calculation the sum in between two dates in two different tables

Hi,

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

Community Support Team

## Re: Calculation the sum in between two dates in two different tables

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.

Super User IV

## Re: Calculation the sum in between two dates in two different tables

Hi,

Regards,
Ashish Mathur
http://www.ashishmathur.com
Frequent Visitor

## Re: Calculation the sum in between two dates in two different tables

Hi
thanks a lot for your support
https://app.powerbi.com/view?r=eyJrIjoiZGE2NDhkZjYtNDlkOC00ODNiLTk0ZDgtOTMzOGEwN2EyY2RhIiwidCI6IjY5Y...

thanks

Super User IV

## Re: Calculation the sum in between two dates in two different tables

Hi,

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

Regards,
Ashish Mathur
http://www.ashishmathur.com
Frequent Visitor

## Re: Calculation the sum in between two dates in two different tables

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

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

Announcements

#### Meet the 2020 Season 1 Power BI Super Users!

It’s the start of a new Super User season! Learn all about the new Super Users and brand-new tiered recognition system.

#### Super User Challenge: Can You Solve These?

We're celebrating the start of the New Super User season with our first ever Super User 'Can You Solve These?' challenge.

#### Power BI Desktop Update - February 2020

We are super excited for our update this month, as we are releasing two of our top community requests!