locked
Basket Matrix variend based on timeline/filter selection RRS feed

  • Question

  • Hi,

    I am using a basket case matrix to check cross sales against a product using

    =CALCULATE (
        DISTINCTCOUNT ( AdDetails[company] ),
        CALCULATETABLE (
            SUMMARIZE ( AdDetails, Table2[Company Grouping]),
            ALL ( titles ),
            USERELATIONSHIP ( AdDetails[ttlname],'FilterTitle'[filterttlname] )
        )
    )

    I am wanting to check varience (last year) either based on a cell range of on a timeline selection.

    Any help.... Im guessing part of it might be having two measures one doing 2015 and one doing 2016.  The question is how to i get the selection data from excel into the dax measure.

    I have tried with 

    =CALCULATE (
        DISTINCTCOUNT ( AdDetails[company] ),
        CALCULATETABLE (
            SUMMARIZE ( AdDetails, Table2[Company Grouping]),
            ALL ( titles ),
            USERELATIONSHIP ( AdDetails[ttlname],'FilterTitle'[filterttlname] ),SAMEPERIODLASTYEAR('Calendar'[Date])
        )
    But the same period last year is bring back the same values as if it wasnt included.

    Thanks



    • Edited by JaK82 Tuesday, August 16, 2016 9:43 AM
    • Moved by David_JunFeng Wednesday, August 17, 2016 3:07 AM
    Tuesday, August 16, 2016 8:22 AM

Answers

All replies

  • Hi JaK82,

    This is the forum to discuss questions and feedback for Excel for Developers, I'll move your question to the MSDN forum for Power Pivot

    https://social.msdn.microsoft.com/Forums/office/en-US/home?forum=sqlkjpowerpivotforexcel

    The reason why we recommend posting appropriately is you will get the most qualified pool of respondents, 
    and other partners who read the forums regularly can either share their knowledge or learn from your interaction with us. 

    Thanks for your understanding.
    Wednesday, August 17, 2016 3:05 AM
  • Thanks, Any help guys?
    Thursday, August 18, 2016 8:39 AM
  • Could you share a bit more about your model? Apparently you have tables AdDetails, Table2, titles, and FilterTitle, but how are these related? And what are you trying to calculate? "using a basket case matrix to check cross sales against a product" is beyond my comprehension...
    Thursday, August 18, 2016 1:19 PM
    Answerer
  • Hi Michiel,

    I am using the tutorial http://www.daxpatterns.com/basket-analysis/

    This works, now I am trying to pull out the data for the same period last year in order to then compare.

    AdDetails, Table2, titles, and FilterTitle,

    Addetails connects to the titles via a direct link and to the filtertitles via a indirect link.

    Table2 contains the company grouping data.

    it where to have the same period last year really.

    Thanks

    Chris

    Thursday, August 18, 2016 1:25 PM
  • JaK, have you made any progress with this?

    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:11 AM
  • Hi Ed,

    No couldn't figfure out how to show last years figures

    Thanks

    Chris

    Monday, October 31, 2016 8:09 AM
  • Hi JaK82,

    please have a look if this helps: http://www.daxpatterns.com/dynamic-segmentation/


    Imke Feldmann TheBIccountant.com

    Saturday, November 19, 2016 8:49 AM
    Answerer