cancel
Showing results for
Did you mean:
Frequent Visitor

## Rolling Inner Join Sum

Hey Guys,

I'm looking to do an inner join on a uniqueID from one table to another and then take the sum of a dollar amount  from the other table.  I want to write this as a DAX measure.

So I have 4 Tables right now like this:

What I want to do is a running inner join on the "ID" on TableA and TableB and sum the "Spent" on TableB , where the running total is 3 months ahead (eg. Jan 2018 sum would be Jan 2018, Feb 2018, Mar 2018, and Apr 2018).  So if I plot a a table it would look like this:

I already have a function to do a normal rolling sum, like this:

```Roll3Sum =
CALCULATE (
SUM ( TableB[Spent] ),
FILTER (
ALL ( 'Date' ),
'Date'[Rank] >= MAX ( 'Date'[Rank] )
&& 'Date'[Rank]
<= MAX ( 'Date'[Rank] ) + 3
)
)```

I wanto to perform this but inner join on ID's on the specified month and roll it 3 months ahead like mentioned above.

*EDIT*

here is some sample data, you will see that in the FinalOutput tab, the TotalSpent column is an inner join on the ID'S from tab A and tab B with a 4 month roll including the selected month and the sum of the Spent column from tab B.

https://www.dropbox.com/s/mvjzeodkvv5flwi/TestExample.xlsx?dl=0

1 ACCEPTED SOLUTION

Accepted Solutions
Super User IV

## Re: Rolling Inner Join Sum

Hi,

Hope this helps.

Regards,
Ashish Mathur
http://www.ashishmathur.com
17 REPLIES 17
Super User IV

## Re: Rolling Inner Join Sum

Hi,

Share TableA and TableB data (in a format that can be pasted in MS Excel) and show the expected result.

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

## Re: Rolling Inner Join Sum

@Ashish_Mathur Hey, I uploaded the sample data onto my original post under *EDIT*.  Thank you!

Super User IV

## Re: Rolling Inner Join Sum

Hi,

In the FinalOutput worksheet, i understand how you got the figures in column C.  How did you arrive at the numbers in column D?  Show the Excel formula in those cells.

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

## Re: Rolling Inner Join Sum

@Ashish_Mathur Hey Ashish, I wrote these example values in Python, but I need to use Power BI for this particular task because I want to be able to dynamically filter visuals.

Super User IV

## Re: Rolling Inner Join Sum

How have you arrived at the numbers in column D?  What logic have you used.  Unless i know the logic, i cannot translate that into DAX formulas.

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

## Re: Rolling Inner Join Sum

Hey Ashish, so the logic is as follows:

FinalOutput Tab Values:

Date (Column B): Dates for the Values in Table A after grouping their Total Available.

Total Available (Column C) : Total Sum of the Total Available from Table A

TotalSpent (Column D): Sum of Spent from Table B after doing an inner Join on ID from table A and table B and rolling the sum of Spent by 4 months.

For Example in that exact spreadsheet:

If you take row 5 where date = 2019-02-28

Total Available is just the sum of Total Available from tab A where date == "2019-02-28",

which are the IDS = [12,18,16] and Total Available =[41,89,66] with a total of 196.

After getting these ID's inner join table B with a 4 month roll:

Filter Table B with dates ['2019-02-28',"2019-03-31",2019-04-30,"2019-05"31] and ID's from table A,

in this case it would ID = [16,16,18,18] with Spent = [10,38,65,38] which is the TotalSpent in the FinalOutput tab

equal to 151

Ratio: Total Spent / Total Available

Thank you for helping out!

Super User IV

## Re: Rolling Inner Join Sum

Hi,

Hope this helps.

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

## Re: Rolling Inner Join Sum

Hey, Ashish

Thanks so much for this, is possible to return blank values if the date does not have 3 month roll available?  So for dates ('2019-03-31', '2019-04-30' and '2019-05-31'), since 3 months in advance do not exist in this dataset.

Thank you!

Super User IV

## Re: Rolling Inner Join Sum

Hi,

Hope this helps.

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

Announcements

#### New Ranks Launched March 24th!

The time has come: We are finally able to share more details on the brand-new ranks coming to the Power BI Community!

#### ‘Better Together’ Contest Finalists Announced!

Congrats to the finalists of our ‘Better Together’-themed T-shirt design contest! Click for the top entries.

#### Arun 'Triple A' Event Video, Q&A, and Slides

Missed the Arun 'Triple A' event or want to revisit it? We've got you covered! Check out the video, Q&A, and slides now.

#### Join THE global Power Platform event series.

Attend for two days of expert-led learning and innovation on topics like AI and Analytics, powered by Dynamic Communities.

#### Community Summit North America

Innovate, Collaborate, Grow. The top training and networking event across the globe for Microsoft Business Applications

Top Solution Authors
Top Kudoed Authors