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 give me some advices.

Best regards,
J.

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

4 Replies

  • MattAllington's avatar
    MattAllington
    Community Champion
    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

    • MichaelJackpbi's avatar
      MichaelJackpbi
      Helper I
      9 years ago

      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's avatar
        MattAllington
        Community Champion
        9 years ago

        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. 

  • 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