Forum Discussion
Create a complex measure
Based on my understanding from what you have written....
first make sure you have an integer ID column in your calendar table. Read my article about that here http://exceleratorbi.com.au/power-pivot-calendar-tables/
now you have an ID column, you can write time shifting formulas like this.
Sales table 1 =
CALCULATE (
SUM ( sales1[sales] ),
FILTER (
ALL ( calendar ),
calendar[ID] <= MAX ( calendar[ID] )
&& calendar[ID]
>= MAX ( calendar[ID] ) - 1
)
)Sales table 2=calculate(sum(sales2[sales]),filter(all(calendar),calendar[ID] <= max(calendar[ID]) ))
Combined Sales = [sales table 1] + [sales table 2]
i cover this concept and how it works in some depth in my book http://xbi.com.au/learndax
MattAllington I think your solution is so good, and there is only one thing that doesnt meet my issue! i used the date field to mapping between Sales1 & Sales2 to Time dimension. I tried to create the calculation as your recommend based on the date field but i was unable to -3 days! Could you give some advice on the DAX for date data type instead of interger data type ??
Thank you so much!.
J.
First create a calendar table with a date field and the ID I mentioned
joint both sales tales to the common calendar table using the date field
write the measure the way I described using the ID column, not the date column.