Forum Discussion

MichaelSamiotis's avatar
MichaelSamiotis
Icon for Resolver I rankResolver I
3 years ago
Solved

Repeating Customers with criteria

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...
General Comment
Need Help
Show and Tell
Tips and Tricks
  • Anonymous's avatar
    Anonymous
    3 years ago

    It is now resolved with the following:

    RETURN
            CALCULATE(COUNTROWS (
                FILTER (
                    UniqueCustomersTable,
                    SELECTEDVALUE ( 'Fact Sales'[Product_ID] ) = [Product_ID]
                )
            ))
Anonymous's avatar
Anonymous
Not applicable
3 years ago

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)