Repeating Customers with criteria

[MichaelSamiotis]

Hello all,

I hope you are well and thank you very much in advance for any advice on the below.

I have a table containing Shop,City,Product_id,Purchase_date. I want to count how many customers from the previous year, have bought the same product in the same shop in the same city and slice this by year.

Below I have a sample data and my DAX for now.

Distinct Customers =
CALCULATE(
DISTINCTCOUNT(Sales[Customer_id]),
FILTER(
Sales,
YEAR(Sales[Event_date]) = YEAR(TODAY()) - 1 &&
Sales[Event_date] >= DATE(YEAR(TODAY()) - 1, 1, 1) &&
Sales[Shop] = EARLIER(Sales[Shop]) &&
Sales[Product_id] = EARLIER(Sales[Product_id]) &&

Sales[City] = EARLIER(Sales[City])
)
)

The EARLIER doesn't seem to work and I get it why but can't get my head around on how to meet the criteria.

Thank you.

[Anonymous · Accepted solution]

It is now resolved with the following:

RETURN

        CALCULATE(COUNTROWS (

            FILTER (

                UniqueCustomersTable,

                SELECTEDVALUE ( 'Fact Sales'[Product_ID] ) = [Product_ID]

            )

        ))

[Anonymous]

The below is my formula till now but the problem is that it returns the same number on each row. I get why but can' t think a way to not to. My table is split by product and year from the dim-calendar table.

Test 1 =

/* How many of the prior year attendees have made an order for an event at the same level

(i.e. at the same racecourse or same raceday) in this event year (event can be in the future, no restrictions on order date) */

/* Calculate the previous year's dates */

VAR PreviousYearJanuary =

    DATEADD (

        STARTOFYEAR ( 'Dim Dates'[Date] ),

        -1,

        YEAR

    )

VAR PreviousYearDecember =

    DATEADD (

        ENDOFYEAR ( 'Dim Dates'[Date] ),

        -1,

        YEAR

    )

/* Calculate the 1st and last date of the calculated table after the previous year */

VAR FilterContextJanuary =

    STARTOFYEAR ( 'Dim Dates'[Date] )

VAR LastCalendarDate =

    CALCULATE(MAX ( 'Fact Sales'[event_date] ),ALL('Fact Sales'))

VAR AllSalesTablePreviousYear =  FILTER(All('Fact Sales'),AND (

            'Fact Sales'[event_date] >=PreviousYearJanuary,

            'Fact Sales'[event_date] <= PreviousYearDecember))

/* Return a calculated table with a distinct combination of the input dimensions */

VAR PreviousYearOrders =

    DISTINCT (

        SELECTCOLUMNS (

            AllSalesTablePreviousYear,

            "Product", [productID],

            "CustomerID",[customer_id]

        )

    )

/* Filter the calculated table with the previous year's dates */

VAR NextYearsOrders =

    DISTINCT (

        SELECTCOLUMNS (

            FILTER(all('Fact Sales'),AND (

            'Fact Sales'[event_date] >= DATEVALUE(FilterContextJanuary),

            'Fact Sales'[event_date] <= DATEVALUE(LastCalendarDate)

        )),

            "Product", [productID],

            "CustomerID",[customer_id]

        )

    )

/* Intersect the two calculated tables */

VAR UniqueCustomersTable =

    DISTINCT(INTERSECT(

        PreviousYearOrders,

        NextYearsOrders

    ))

/* Count the number of rows of the final table */

RETURN

COUNTROWS(UniqueCustomersTable)

[amitchandak]

@MichaelSamiotis , refer if this approach can help  , use datesytd

You can have measure this year vs last year using these

YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD('Date'[Date],"12/31"))
Last YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(dateadd('Date'[Date],-1,Year),"12/31"))
This year Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(ENDOFYEAR('Date'[Date]),"12/31"))
Last year Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(ENDOFYEAR(dateadd('Date'[Date],-1,Year)),"12/31"))
YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),previousyear('Date'[Date]))
Last to last YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(dateadd('Date'[Date],-2,Year),"12/31"))
Year behind Sales = CALCULATE(SUM(Sales[Sales Amount]),dateadd('Date'[Date],-1,Year))
Year behind Sales = CALCULATE(SUM(Sales[Sales Amount]),SAMEPERIODLASTYEAR('Date'[Date]))

Customer Retention Part 1:
https://community.powerbi.com/t5/Community-Blog/Customer-Retention-Part-1-Month-on-Month-Retention/b...
Customer Retention Part 2: Period over Period Retention :https://community.powerbi.com/t5/Community-Blog/Customer-Retention-Part-2-Period-over-Period-Retenti...

Customer Retention Part 5: LTD Vs Period Retention
https://community.powerbi.com/t5/Community-Blog/Customer-Retention-Part-5-LTD-and-PeriodYoY-Retentio...

Customer Retention with Dynamic Segmentation, New/Lost/Retain Customer Count: https://youtu.be/EyL7KMw877Q

Time Intelligence, Part of learn Power BI https://youtu.be/cN8AO3_vmlY?t=27510
Time Intelligence, DATESMTD, DATESQTD, DATESYTD, Week On Week, Week Till Date, Custom Period on Period,
Custom Period till date: https://youtu.be/aU2aKbnHuWs&t=145s

[MichaelSamiotis]

@amitchandak this is a different question and not a YTD exactly. From the last years distinct customers that bought from a specific store, and city, count if the same customer has returned this year (count as 1 no matter how many times has he bought this product as long as it is the same product in the same city in the same store) and also include any future sales that migh have in place e.g. for subscriptions that are beyond this year.