Create a complex measure

[MichaelJackpbi]

Hi friends,
I have 2 tables that first one contains the Sales1, productID,date and another contains Sales2,ProductID,date and both have the relationship with time and product dimension

Please give me some advices.

Best regards,
J.

[v-ljerr-msft]

Hi @MattAllington, 

Excellent solution! It is much simpler to add an integer ID column to calendar table than just use DAX Date functions to handle this scenario. Great to know it!

Just in case, as @MichaelJackpbi needs SUM(Sales2) start from first day of Sales2 and end at 13/5/2016, the "Sales table 2" measure you provided above should use "MAX ( calendar[ID] ) - 2" in the condition of the filter.

Sales table 2 =
CALCULATE (
    SUM ( sales2[sales] ),
    FILTER ( ALL ( calendar ), calendar[ID] <= MAX ( calendar[ID] ) - 2 )
)

Regards

[MattAllington · Accepted solution]

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

[MichaelJackpbi]

@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.

[MattAllington]

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.