Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

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

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

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/
Anonymous
Not applicable

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

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/
Anonymous
Not applicable

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

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/
Anonymous
Not applicable

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

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/
Anonymous
Not applicable

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

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/
Anonymous
Not applicable

@Ashish_Mathur Hey Ashish, appreciate all the help so far, its been very insightful.  Just 1 more question, is it possible to have slicers work with this DAX equation?  I will upload the same .PBIX file you had, but add 1 more column "SalesPerson" in the Available Table and name the table Available (2).

 

I want to able to create a slicer (or multiple) that will do what you've done above, but be able to slice and show their  Individual data in the Merge1 Summary table.

 

Thanks!

 

https://www.dropbox.com/s/kqtrf7kuai326np/Available%20and%20spend%20v1.pbix?dl=0 

Hi,

Share the source Excel file.


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

@Ashish_Mathur 

Hey Ashish,

Here is the excel file:

https://www.dropbox.com/s/ycfjzjoe8ewzj13/TestExample_v1.xlsx?dl=0

 

As mentioned before, I would like to able to slice on the values in "SalesPerson" and "Position" but have the values in the FinalOutput if there is no slicing.

 

Thank you so much, I really appreciate this.

Hi,

How is it that in Tab B, there are no Sales Person and Position columns?


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

@Ashish_Mathur 

Hey Ashish 

 

Yes, that is part of the issue.  I need to be able to slice on Table A (SalesPerson and Position) and after slicing, table A would inner join with Table B on ID with a 4 roll month and take the sum of Spent (Table B).  You were able to produce a solution in the previous post(s) but without slicing other columns.  Is what i'm asking possible in PowerBI?

Hi,

Unless we have those 2 columns in TabB, the end result will not make any sense.


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

@Ashish_Mathur 

 

So it's not possible to be able to slice on Tab A (SalesPerson or Position) and then use the solution you had gave me in the previous post?

 

Hi,

Since those 2 columns are only in TabA, the calculations based on only that Table can be slicer by the two new columns.  The calculations on TabB and ratio will not be slicer because these 2 columns are not on TabB.

Hope this clarifies.


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

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.