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