Should I be using date table columns or fact table in this measure?

[Anonymous]

Hello Community  -  This measure is from a blog discussing new customers.  Power BI: Cohort Analysis Full Tutorial - Finance BI (finance-bi.com)

I've modified it only in terms of I am using (mostly) columns from dimension tables, but in some cases I've used fact tables.  

In the original formula, the author has used fact tables.   But I have dimension tables for date and customers.   

Any guidance on what to use (and why)?

New Customers Average Spend =

VAR acquisitionMonth =

    SELECTEDVALUE ( 'Dim_Order Date Table'[Year Month order] )

VAR selectedFutureMonth =

    SELECTEDVALUE ( 'Future months'[Value] )

VAR currentCustomers =

    VALUES ( 'SalesOrdersALL'[Bill To Customer_CustNum] )

VAR pastCustomers =

    CALCULATETABLE (

        VALUES ( SalesOrdersALL[Bill To Customer_CustNum] ),

        ALL ( 'Dim_Order Date Table'[Month & Year], 'Dim_Order Date Table'[MonthnYear] ),

        'Dim_Order Date Table'[Year Month order] < acquisitionMonth

    )

VAR newCustomers =

    EXCEPT ( currentCustomers, pastCustomers )

VAR customersFuturePeriods =

    CALCULATETABLE (

        VALUES ( SalesOrdersALL[Bill To Customer_CustNum] ),

        ALL ( 'Dim_Order Date Table'[Month & Year], 'Dim_Order Date Table'[MonthnYear] ),

        'Dim_Order Date Table'[Year Month order] = acquisitionMonth + selectedFutureMonth

    )

VAR newCustomersBuyingInFuturePeriods =

    INTERSECT ( newCustomers, customersFuturePeriods )

RETURN

CALCULATE(SUM(SalesOrdersALL[Net Price]),

ALL('Dim_Order Date Table'[Month & Year],'Dim_order date Table'[MonthnYear]),

'Dim_Order Date Table'[Year Month order]= acquisitionMonth+selectedFutureMonth,SalesOrdersALL[Bill To Customer_CustNum]

 IN newCustomersBuyingInFuturePeriods)

/

COUNTROWS(newCustomersBuyingInFuturePeriods)

[Anonymous]

@amitchandak   Thanks Amit!   What I took away from comparing your video to the measure I used is that I should probably have created my measure using columns from the fact table.  It seems that you created your custom columns in your sales fact table, whereas I created my custom dates in my date dimension table.  

[amitchandak · Accepted solution]

@Anonymous , refer if this can help

Power BI Cohort Analysis, Customer Retention %- https://youtu.be/qY1SDF1cwsg

Period Of Stay – Cohort Analysis: https://community.powerbi.com/t5/Community-Blog/Customer-Retention-Part-3-Period-Of-Stay-Cohort-Analysis/ba-p/1393410