Customers with only 1 product between a certain period

[gbarr12345]

Hello,

I have a measure that shows the customers that purchased only 1 product between a certain time and it works.

I'm just trying to advance the measure even more to filter it between a certain period of time? 

In this case the table is called Dimension_Period and the field is called FYPeriod and the dates are written as YearMonth such as 202403 and 202405. I would like to write the measure to include the sales between these 2 periods?

I have pasted my working code down below. Any help to modify it further would be greatly appreciated:

Not more than 1 purchase of product =

COUNTROWS(

    FILTER(

        SUMMARIZE('Module Sales with Inventory', 'Dimension Customer'[customer],

        "Total Sales", [Total Amount] ),

        [Total Amount] <= 1 ) )

[Ashish_Mathur]

Hi,

Share the download link of the PBI file.

[gbarr12345]

Hi Ashish,

Please find the Google Docs link attached - https://drive.google.com/file/d/1oAMGapVUty-exSWIfGtDok1XmsI54E3R/view?usp=drive_link

I created sample data based on the same tables and fields I'm trying to use.

Thank you!

[Ashish_Mathur]

Access Denied message.

[gbarr12345]

Apologies, please try now.

[AndyEagleton]

I reckon it is probably better to do the inverse of this then do subtraction. Something like:

Customers with more than 1 purchase =

VAR DateFilter =
    TREATAS ( {202003, 202004}, Dates[Year-Month] )

RETURN
    CALCULATE (
        COUNTROWS (
            FILTER (
                SUMMARIZE ( Sales, Customers[CustomerKey] ),
                CALCULATE ( COUNTROWS ( Sales ) ) > 1
            )
        ),
        DateFilter 
    )

[v-rongtiep-msft]

Hi @gbarr12345 ,

Please have a try.

product in Period =
CALCULATE (
    COUNTROWS (
        FILTER (
            SUMMARIZE (
                'Module Sales with Inventory',
                'Dimension Customer'[customer],
                "Total Sales", [Total Amount]
            ),
            [Total Amount] <= 1
        )
    ),
    FILTER (
        'Dimension_Period',
        'Dimension_Period'[FYPeriod] >= 202403
            && 'Dimension_Period'[FYPeriod] <= 202405
    )
)

How to Get Your Question Answered Quickly - Microsoft Fabric Community

If it does not help, please provide more details with your desired output and pbix file without privacy information (or some sample data) .

Best Regards
Community Support Team _ Rongtie

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[gbarr12345]

Hi there,

I ran that measure and am getting the following: