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