Calculate sum based on start date

[#1]

I have two tables - sales and location.

Location: Has Location Code and start Date

Sales : Has Location Code and Sales for last year

I loaded sales as cross table and set up a master calendar.

I have managed to calculate the sum of sales for the last 4 months. But for this example I have hardcoded 4. I actually need to go back as far the start month for each location. For example, Location 11 needs to have the sales calculated from period 201406. How do I achieve this with the expression I have currently used? =sum({$<[PeriodID] = {">$(=Max([PeriodID])-4)"}>}Sales)

I have attached the file with this post. I also need to consider new sales data will be added for 2015.

[maxgro]

and what's wrong with my expression?

sum(if(makedate(left(Period,4),Right(Period,2))>[Start Date], Sales))

or

sum(if(makedate(left(Period,4),Right(Period,2))>=[Start Date], Sales))

can you give an example of an incorrect

location, period, sales, total sales

for my exp?

[#3]

The expression you provided is not working for all locations. For example in the below example , it needs to calculate from period 201408 onwards only.

[maxgro · Accepted solution]

last try

sum(if(makedate(left(Period,4),Right(Period,2))>=

  if(month([Start Date])>month(today()),

       makedate(year(today())-1,month([Start Date])),

       makedate(year(today()),month([Start Date])) ),

  Sales))

[sujeetsingh]

I can not reload the file since i do not have the sources.

Well let me give you a sample in couple of minutes.

[sujeetsingh]

Here is the sample.

Note i do not implemented the Master Calender table.

But it will resolve your issue in a best way.

[#7]

Thank you Sujeet. This is helpful.

[#8]

This is it.Thank you so much Massimo Grossi 🙂