Forum Discussion

qwaiti27231's avatar
qwaiti27231
Icon for Helper III rankHelper III
9 years ago
Solved

I need the formulae for

First order date for a particular customer

customer order frequency ratio

 

order table and customer table is different

 

 

  • Hey, 

     

    here you will find a little example. The model in that file has some tables, e.g. Customer and FactWithDate. Both tables are related: Customer (one) <- FactWithDates (Many) with CrossFilterDirection  = Single.

     

    There is a calculated column in the customer table "First OrderDate Cust", that retrieves the first Date from the factTable:

    First OrderDate Cust = MINX(RELATEDTABLE('FactWithDates'),'FactWithDates'[Date]) 

    The DAX function RELATEDTABLE(...) leverages the existing relationship between both tabels. Due to the fact that the function returns a table with all the Dates the iterator function MINX is used to determine the first date.

     

    There is also a calculated column in the table FactWithDates "First OrderDate Fact":

    First OrderDate Fact = 
    CALCULATE(
        MIN('FactWithDates'[Date])
        ,ALLEXCEPT('FactWithDates','FactWithDates'[Customer])
    )

     Here it is a little more complex, due to fact that a Filter Context has to be introduced, done by CALCULATE(), but it is also necessary that all existing filter have to be removed except the current customer, to gain access to all the values from the Date column. This is achieved by the ALLEXCEPT() function.

     

    Hope this gets you started.

     

    Your request for a frequency ratio is a little vague, e.g. frequency ratio = order numbers per month / order numbers per year or something completely different ;-)

     

    Regards

1 Reply

  • Hey, 

     

    here you will find a little example. The model in that file has some tables, e.g. Customer and FactWithDate. Both tables are related: Customer (one) <- FactWithDates (Many) with CrossFilterDirection  = Single.

     

    There is a calculated column in the customer table "First OrderDate Cust", that retrieves the first Date from the factTable:

    First OrderDate Cust = MINX(RELATEDTABLE('FactWithDates'),'FactWithDates'[Date]) 

    The DAX function RELATEDTABLE(...) leverages the existing relationship between both tabels. Due to the fact that the function returns a table with all the Dates the iterator function MINX is used to determine the first date.

     

    There is also a calculated column in the table FactWithDates "First OrderDate Fact":

    First OrderDate Fact = 
    CALCULATE(
        MIN('FactWithDates'[Date])
        ,ALLEXCEPT('FactWithDates','FactWithDates'[Customer])
    )

     Here it is a little more complex, due to fact that a Filter Context has to be introduced, done by CALCULATE(), but it is also necessary that all existing filter have to be removed except the current customer, to gain access to all the values from the Date column. This is achieved by the ALLEXCEPT() function.

     

    Hope this gets you started.

     

    Your request for a frequency ratio is a little vague, e.g. frequency ratio = order numbers per month / order numbers per year or something completely different ;-)

     

    Regards