Forum Discussion

MichaelJackpbi's avatar
MichaelJackpbi
Helper I
9 years ago
Solved

Create a complex measure

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...
Need Help
  • MattAllington's avatar
    MattAllington
    9 years ago

    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

v-ljerr-msft's avatar
v-ljerr-msft
Microsoft Employee
9 years ago

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.:smileyhappy:

 

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

 

Regards