Forum Discussion
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!
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!
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.
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?
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.
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.