I have current transactional sales data from our CRM, and a years worth of weekly historical sales in excel. I have added columns into excel with the week number and season. I have joined this into my CRM transactional table. The CRM table has a sales date.

I would like to be able to select a week- eg week 5, and the table to display current sales up to the end of that week, with the relevant week from the excel file. How do I categorise my sales dates into these custom weeks.

I have tried an inline statement, or an if statement in my load clause and I cant seem to get it working - any ideas?

It's a bit tricky to say exactly what you need without seeing the tables and code, however if you have data sorted by weeks in the excel and by date in the CRM, you can use the Week(Date) to get week numbers also for the sales dates.

And for accumulation you can use the Rangesum(Above(Sum(Sales),0,$(=Max(weeknumber))) which will sum all sales date up to and including the highest selected weeknumber.