locked
Time Intelligence not Working on measures with unlinked tables RRS feed

  • Question

  • Hello,

    I am having difficult to make this thing work. I have a sales table and a product category table that are not linked directly. I have created a measure to get last month sales, slicing it in a pivot table with product category dimension. However it is not working. 

    The expression bellow is what I am trying to achieve.

    =CALCULATE(SUM('Sales SalesOrderHeader'[TotalDue]);DATEADD('CALENDAR'[DATE];-1;YEAR);Sales SalesOrderDetail)

    Is there any way around this ?

    Thanks in Advance

    Tuesday, July 5, 2016 9:18 PM

Answers

  • Is it the time intelligence that is not working, or the slicing on product category?

    Your formula looks to compute the sales for the previous year (which you could also use SAMEPERIODLASTYEAR for), not the last month. When there's no relationship or chain of relationships between product category and sales, the results will obviously not be sliced on product category. It is possible to create the effect of a relationship through DAX functions, but you would need information to link sales to a product category anyway.

    Wednesday, July 6, 2016 7:02 PM
    Answerer

All replies

  • Is it the time intelligence that is not working, or the slicing on product category?

    Your formula looks to compute the sales for the previous year (which you could also use SAMEPERIODLASTYEAR for), not the last month. When there's no relationship or chain of relationships between product category and sales, the results will obviously not be sliced on product category. It is possible to create the effect of a relationship through DAX functions, but you would need information to link sales to a product category anyway.

    Wednesday, July 6, 2016 7:02 PM
    Answerer
  • Hi Michiel,

    Thank you for replying. 

    The table sales and category are linked indirectly like bellow: 

    Sales 1-->--*  Sales Detail

    Product Info 1-->--* Sales Detail

    Product Subcategory 1-->--* Product Info 

    Product category 1-->--*  Product Subcategory

    I have created a mesure for current sales "=CALCULATE(SUM('Sales SalesOrderHeader'[TotalDue]);'Sales SalesOrderDetail';'CALENDAR')"  and I slice sales on product category it does function correctly. However when I use time intelligence for previous year ( for example using SAMEPERIODLASTYEAR ) this is not working.

    Any idea where am I doing it wrong ?

    Tks

    Wednesday, July 6, 2016 7:32 PM
  • Cybersource, have you made any progress with this?

    You can send a Frown to the Power Pivot team if you run into any more troubles. Thanks!


    Ed Price, Azure Development Customer Program Manager (Blog, Small Basic, Wiki Ninjas, Wiki)

    Answer an interesting question? Create a wiki article about it!

    Sunday, October 30, 2016 10:54 AM