Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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
Solved! Go to 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.
Hi,
Share some data, describe the question and show the expected result.
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
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.
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.
@Syndicate_Admin See if this helps: New and Returning Customers - Microsoft Power BI Community
User | Count |
---|---|
98 | |
90 | |
82 | |
73 | |
67 |
User | Count |
---|---|
115 | |
102 | |
98 | |
71 | |
67 |