cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
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.

Highlighted
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 IV
Super User IV

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 IV
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
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
Exclusive LIVE Community Event #3 – Ask Arun Anything

Exclusive LIVE Community Event #3 – Ask Arun Anything

Join us in the third Triple A event!

Meet the 2020 Season 1 Power BI Super Users!

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.

January 2020 Community Highlights

January 2020 Community Highlights

Make sure you didn't miss any of the things that happened in the community in January!

Difinity Conference

Difinity Conference

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

Top Solution Authors
Top Kudoed Authors