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

c.jpg

 

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:

 

Capture.JPG

 

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.

 

Here is the dropbox link:

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

1 ACCEPTED SOLUTION

Accepted Solutions
Super User IV
Super User IV

Re: Rolling Inner Join Sum

Hi,

You are welcome.  You may download my PBI file from here.

Hope this helps.

Untitled.png


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

View solution in original post

17 REPLIES 17
Super User IV
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
https://www.linkedin.com/in/excelenthusiasts/
jk125
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
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
https://www.linkedin.com/in/excelenthusiasts/
jk125
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
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
https://www.linkedin.com/in/excelenthusiasts/
jk125
Frequent Visitor

Re: Rolling Inner Join Sum

@Ashish_Mathur 

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

Re: Rolling Inner Join Sum

Hi,

You may download my PBI file from here.

Hope this helps.

Untitled.png


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

Re: Rolling Inner Join Sum

@Ashish_Mathur 

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

Re: Rolling Inner Join Sum

Hi,

You are welcome.  You may download my PBI file from here.

Hope this helps.

Untitled.png


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

View solution in original post

Helpful resources

Announcements
New Ranks Launched March 24th!

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!

‘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

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 Microsoft Power Platform event series.

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

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