Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago

Tracking Sales Returns over Time

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 SalesProductSalesSales Return MonthSR Value
1/01/2014A200001/06/20143000
1/01/2014B300001/01/20154000
1/01/2014C400001/02/201510000
1/01/2015X100001/03/20151000
1/01/2015Y150001/02/20161500

 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: 

 

Year1234567891011121314
2014000003000300030003000300030003000700017000
201500100010001000100010001000100010001000100010002500

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: 

Year361314
2014 3000700017000
20151000  2500

 

Any help would be appreciated!  

 

1 Reply

  • 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])))