cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
mamoshareef Frequent Visitor
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])
 
Thanks in advance 
 

1.jpg

2.jpg

1 ACCEPTED SOLUTION

Accepted Solutions
mamoshareef Frequent Visitor
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
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] )))

View solution in original post

7 REPLIES 7
Community Support Team
Community Support Team

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

@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 Frequent Visitor
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
Community Support Team

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

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

Super User
Super User

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

Hi,

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


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
mamoshareef Frequent Visitor
Frequent Visitor

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

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

Test wash Report.jpg
thanks 

Super User
Super User

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
https://www.linkedin.com/in/excelenthusiasts/
mamoshareef Frequent Visitor
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
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] )))

View solution in original post

Helpful resources

Announcements
New Topics Started Badges Coming

New Topics Started Badges Coming

We're releasing new versions of the badge that everyone's talking about. ;) Check your inbox for notifications.

MBAS 2020

Save the new date (and location)!

Our business applications community is growing—so we needed a different venue, resulting in a new date and location. See you there!

Difinity Conference

Difinity Conference

The largest Power BI, Power Platform, and Data conference in New Zealand

Top Solution Authors
Top Kudoed Authors (Last 30 Days)