Tracking Sales Returns over Time

[Anonymous]

Hi, 

I am trying to replicate certain analysis I perform on Excel relating to the aging of Sales Return. I have come pretty close, but with gaps. 

I have Sales and Sales Return data for Capital goods in one table as follows: 

Year of Sales	Product	Sales	Sales Return Month	SR Value
1/01/2014	A	20000	1/06/2014	3000
1/01/2014	B	30000	1/01/2015	4000
1/01/2014	C	40000	1/02/2015	10000
1/01/2015	X	10000	1/03/2015	1000
1/01/2015	Y	15000	1/02/2016	1500

 Note: Year of Sales and Sales Return Months have been created from Sales date and SR date in my source (not in above display). 

Using the above data, I create the following output in Excel which shows the Cumulative Sales Return amount against the 'Year of Sales' (Year when sales originated) in Rows and 'Number of months' in Columns: 

Year	1	2	3	4	5	6	7	8	9	10	11	12	13	14
2014	0	0	0	0	0	3000	3000	3000	3000	3000	3000	3000	7000	17000
2015	0	0	1000	1000	1000	1000	1000	1000	1000	1000	1000	1000	1000	2500

Note: For sales that originated in 2014, the first Return was 6 months later (1/06/2014). Hence, we have the SR amount of 3000 populated against 2014 and 6. The next such return occoured 13 months later (worth 4k in 1/01/2015) and the next 14 months later (worth 10k). These were accordingly cumulated. Similar analysis for 2015. 

To replicate the same in Power BI, in my Sales table, I created a calculated column that returns the difference between 'Year of Sales' and 'Sales Return Month' in months and added 1 to it. Let's call this column 'Month_Diff'.

Furthermore, dragged this field to the Column field in a matrix and the 'Year Sales' to the rows. Added a quick measure for cumulative sales return: 

Running Total =

CALCULATE(

    SUM('Table'[SR Value]),

    FILTER(

        ALLSELECTED('Table'[Month_Diff]),

        ISONORAFTER('Table'[Month_Diff], MAX('Table'[Month_Diff]), DESC)

    )

)

However, the output I am getting is close, but not exact: 

Year	3	6	13	14
2014		3000	7000	17000
2015	1000			2500

Any help would be appreciated!  

[amitchandak]

@Anonymous ,

Create new column like

Month diff= datediff([Year of Sales], [Sales Return], Month)

And use a year from Date table of creating one from Year of Sales

and try a measure like

calculate(sum(table[SR Value]), filter(Table, [Month diff] <=max([Month diff]) && [Year of Sales] = max([Year of Sales])))