locked
DAX calculation between 2 dates RRS feed

  • General discussion

  • Hi,

    Lets say I have a dim.customer table with "customer date" and a fact.Sales table with "posting date".

    I´d like to see the sales of the new customers, using dim.customer."customer date" related to fact.Sales.

    both tables are linked to Dim.date.

    I tried something like this (based on http://projectbotticelli.com/knowledge/dax-in-action-data-analysis-expressions-video-tutorial)

    , but didnt work:

    testmeasure:=CALCULATE(SUM([Invoiced Quantity]);

                            FILTER(Customer;

                                CALCULATE(COUNTROWS('Sales'))>0&&

                               CALCULATE(

                                   COUNTROWS('Sales');

                                   FILTER(

                                       ALL(Date); Date[PK_Date]< MIN(Date[PK_Date])

                                       )

                                       )=0

            )

    )

    ideas?

    thanks!

    Monday, February 11, 2013 4:36 PM

All replies

  • to get the sales of new customers at a given date you may also try this:

    1) add a calculated column to your customers-table to get the date of the first purchase:
    =MINX(RELATEDTABLE('Internet Sales'); 'Internet Sales'[Order Date])

    2) define an inactive relationship between this new column and your Date[Date] column

    3) create a calculated measure as follows:
    SA New Customers:=CALCULATE(SUM('Internet Sales'[Sales Amount]); USERELATIONSHIP(Customer[Date First Purchase Calc]; 'Date'[Date]))

    this should also perform much better as no FILTER() etc. is used

    hth,
    gerhard


    - www.pmOne.com -

    Monday, February 11, 2013 6:30 PM
    Answerer
  • ok thanks, I will give a try

    Tuesday, February 12, 2013 3:39 PM