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.

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/
Highlighted
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.

Super User Challenge: Can You Solve These?

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

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!

Power Platform Online Conference

Power Platform Online Conference

Join us for the first ever Power Platform Online Conference!

Top Solution Authors