New Customer Calculation

[Shelley]

Hi All, I'm probably doing something dumb, but I cannot figure out how to make this formula work properly. This is supposed to ignore any slicer/filter on the ProfitCenterGroup Table, but when I click the slicer, it doesn't seem to ignore it. In addition, it calculates way too low, even if it were considering the filter setting. Perhaps this is something in the relationships?? 

Absolute New Customers =
COUNTROWS (
FILTER (
ADDCOLUMNS (
VALUES ( PLS_Analytics[End_Customer_Key] ),
"PreviousSales",
CALCULATE (
COUNTROWS ( PLS_Analytics ),
ALL ( 'ProfitCenterGroup Table' ),
FILTER (
ALL ( 'RA_Daily_Calendar' ),
'RA_Daily_Calendar'[Date] < MIN ( 'RA_Daily_Calendar'[Date] )
)
)
),
ISBLANK ( [PreviousSales] )
)
)

Here's an image of the relevant tables and relationships.

 

Help is appreciated. Thanks in advance!

[Anonymous · Accepted solution]

HI @Shelley,

Perhaps you can try to use below measure if it works on your side.

Absolute New Customers =
COUNTROWS (
    FILTER (
        SUMMARIZE (
            PLS_Analytics,
            PLS_Analytics[End_Customer_Key],
            "PreviousSales", CALCULATE (
                COUNTROWS ( PLS_Analytics ),
                ALL ( 'ProfitCenterGroup Table' ),
                FILTER (
                    ALL ( 'RA_Daily_Calendar' ),
                    'RA_Daily_Calendar'[Date] < MIN ( 'RA_Daily_Calendar'[Date] )
                )
            )
        ),
        ISBLANK ( [PreviousSales] )
    )
)

Regards,

Xiaoxin Sheng

[Anonymous]

It works for me. Thank you. In my case study it required a little change . I needed to change below line

MIN ( 'RA_Daily_Calendar'[Date] )

and use a calculate measure to display results for each month (without any change it displaied repeated total counted value).