Help with calculating new customers based on their purchases only in the last month

[Syndicate_Admin]

Dear

I want to calculate the new customers who bought out of 1 in the last month and that the months before the current month are blank.

In excel I make the detailed formula in the image:

The sum corresponds to all the months prior to the current one and if the current month is greater than 1.

Please if you can help me with a Dax for this one or some alternative to replicate this in PowerBi.

Thanks a lot

[Ashish_Mathur]

Hi,

Share some data, describe the question and show the expected result.

[Syndicate_Admin]

thank you very much for the help, but I have a doubt.

&& [Customer] = EARLIER( ( 'Table'[Customer] )

In that part my custumer table has to be a measure? I can't add only the greetings field

[v-zhangti]

Hi, @Syndicate_Admin 

Can you provide easy PBIX files for testing? I would like to know the format of custumer table. Looking forward to your reply.

Best Regards,

Community Support Team _Charlotte

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

[v-zhangti · Accepted solution]

Hi, @Syndicate_Admin 

I have simulated some data that I hope fits your description.

Column = VAR n1 = 
CALCULATE (
    COUNT ( 'Table'[Customer] ),
    FILTER (
        'Table',
        [Purchase time].[MonthNo]
            = MONTH ( TODAY () ) - 1
            && [Customer] = EARLIER( ( 'Table'[Customer] )
    )
))  Var n2 =
CALCULATE (
    COUNT ( 'Table'[Customer] ),
    FILTER (
        'Table',
        [Purchase time].[MonthNo]
            <> MONTH ( TODAY () ) - 1
            && [Purchase time].[MonthNo] <> MONTH ( TODAY () )
            && [Customer] = EARLIER ( 'Table'[Customer] )
    )
)
Return IF(n1>=1&&n2=0,"New",BLANK())

If the method I provided above can't solve your problem, what's your expected result? Could you please provide more details for it?

Best Regards,

Community Support Team _Charlotte

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

[Greg_Deckler]

@Syndicate_Admin See if this helps: New and Returning Customers - Microsoft Power BI Community