cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
misiek5510
Helper I
Helper I

Show New Entries Each Month

Hi All,

 

I'm looking for a measure that will show me new entries each month, but will look into whole year first. So if for example new unique ID (customer) will place an order and he never purchased anything this year I need the output to be "new" and if his records are already in the system in current year or last 12 months to be "old".

I tried the below but it gives an error "Function Sum can not work with values of type string". 

new / old = if(isblank(CALCULATE(sum(DATACOMP[User ]),DATESMTD(dateadd(DATACOMP[Date],-12,MONTH)))),"New","Old")
4 REPLIES 4
amitchandak
Super User
Super User

@misiek5510 , with slight change in formula, in these you can get

 

Customer Retention Part 1:
https://community.powerbi.com/t5/Community-Blog/Customer-Retention-Part-1-Month-on-Month-Retention/b...
Customer Retention Part 2: Period over Period Retention :https://community.powerbi.com/t5/Community-Blog/Customer-Retention-Part-2-Period-over-Period-Retenti...

 

 

Rolling 12 = CALCULATE(sum(Sales[Sales Amount]),DATESINPERIOD('Date'[Date ],MAX('Date'[Date ]),-12,MONTH))

 

Rolling 12 till last month = CALCULATE(sum(Sales[Sales Amount]),DATESINPERIOD('Date'[Date ],eomonth(MAX('Date'[Date ]),0) ,-12,MONTH))

 

This is your last period

Rolling 12 till last month

 

Current period

MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD('Date'[Date]))

 

rest should be same as blog

 



New Power BI Features
Datamarts: https://youtu.be/8tskWsJTEpg
Field Parameters : https://youtu.be/lqF3Wa1FllE?t=70
Did I answer your question? Mark my post as a solution! Appreciate your Kudos!! !!
Dashboard of My Blogs !! Connect on Linkedin !! Proud to be a Super User!
Want To Learn Power BI
Learn Power BI Beginners !! Advance Power BI Concepts !! Power BI For Tableau User !! Learn Power BI in Hindi !!
!! Subscribe to my youtube Channel !!

The measures above seem to be looking at sales (numbers) about and I'm checking for the username which would be a string so I can not use calculate or sum on it. 

 

Rolling 12 till last month = CALCULATE(sum(Sales[Sales Amount]),DATESINPERIOD('Date'[Date ],eomonth(MAX('Date'[Date ]),0) ,-12,MONTH))

 

Am I doing something wrong?

@misiek5510 , You can use count

 

Rolling 12 till last month = CALCULATE(Count(Sales[User name]),DATESINPERIOD('Date'[Date ],eomonth(MAX('Date'[Date ]),0) ,-12,MONTH))

 

If you have checked blog. The basic idea is you have transactions in the current period say a month and do not have transactions before. 

 

 

This Period = CALCULATE([Sales],Datesmtd('Date'[Date]))

Last Period = CALCULATE([Sales],DATESINPERIOD('Date'[Date ],eomonth(MAX('Date'[Date ]),0) ,-12,MONTH))

New Customer This Period = Sumx(VALUES(Customer[Customer Id]),if(ISBLANK([Last Period]) && not(ISBLANK([This Period])) , 1,BLANK()))

Retained Customer This Period = Sumx(VALUES(Customer[Customer Id]),if(not(ISBLANK([This Period])) && not(ISBLANK([Last Period])) , 1,BLANK()))

Lost Customer This Period = Sumx(VALUES(Customer[Customer Id]),if(ISBLANK([This Period]) && not(ISBLANK([Last Period])) , 1,BLANK()))

 

 

now measure [sales] can be replaced with any measure to make sure it gives result when we have transaction



New Power BI Features
Datamarts: https://youtu.be/8tskWsJTEpg
Field Parameters : https://youtu.be/lqF3Wa1FllE?t=70
Did I answer your question? Mark my post as a solution! Appreciate your Kudos!! !!
Dashboard of My Blogs !! Connect on Linkedin !! Proud to be a Super User!
Want To Learn Power BI
Learn Power BI Beginners !! Advance Power BI Concepts !! Power BI For Tableau User !! Learn Power BI in Hindi !!
!! Subscribe to my youtube Channel !!

Thank you for reply. The code above makes sense and I'm trying to do it on my end and it fails due to not being able to use the "This Period" Measure. 

 

New Customer This Period = Sumx(VALUES(Customer[Customer Id]),if(ISBLANK([Last Period]) && not(ISBLANK([This Period])) , 1,BLANK()))

 

I tried cleating another measure as "this period" and whatever I do its not avaiable to chose from where I marked it red above.  

misiek5510_0-1639054958725.png

 

Helpful resources

Announcements
Power Platform Conf 2022 768x460.jpg

Join us for Microsoft Power Platform Conference

The first Microsoft-sponsored Power Platform Conference is coming in September. 100+ speakers, 150+ sessions, and what's new and next for Power Platform.

Power BI Dev Camp Session 23 768x460.jpg

Check it Out!

Mark your calendars and join us on Thursday, June 30 at 11a PDT for a great session with Ted Pattison!

Top Solution Authors
Top Kudoed Authors