Calculation if there are new countries in the last 12 months

[Arwen16]

Hello,

We have a table where it is stored by month the countries and the numbers of users registered in that month.

I would like to be able to get in a measure, if there are new countries in the latest month, that weren't in the database 12 months ago.

On the Power BI dashboard I want to add a card with the text: "there are no new countries in the last 12 months" if there are no new countries and nothing otherwise.

I was doing this counting the number or countries but the problem is that our users can deregister, which makes that maybe there are countries existing 12 months ago, that do not exist anymore in the latest month.

That is why I think I need to make a comparison of the exact countries 12 months ago and now and be able to count countries that exist this month but didn't exist 12 months ago.

Here some data to be able to be able to understand our table:

In this case I would like a measure that returns a 1 indicating that there are new countries in comparison to the last 12 months (NO is in 2022 but not in 2021). We can also see that there are 4 countries for both months but CH is not anymore listed in 2022.

I would really appreciate tips, how to address this issue with DAX.
Thanks!

date	country_iso2	count
2022-08-01	UK	500
2022-08-01	AT	100
2022-08-01	NO	50
2022-08-01	LI	15
2021-08-01	UK	800
2021-08-01	CH	20
2021-08-01	AT	50
2021-08-01	UK	300

[Anonymous]

Hi @Arwen16,

According to your description, You want to count countries that exist this month but did not exist last year,right?

Here are my steps you can follow:

(1)This is my test data.

(2)We can create a measure: “Measure”.

Measure = var max_date=  MAXX(ALL('Table'),'Table'[date])

var _current_month = FILTER('Table','Table'[date] <= max_date && 'Table'[date] >= max_date -30)

var _pre_year = FILTER('Table', YEAR('Table'[date]) = YEAR(max_date)-1 )

return

IF( COUNTROWS(_current_month)>0 && COUNTROWS(_pre_year) =BLANK() ,"NO" , BLANK())

(3)Then the result is as follows.

If this method does not meet your needs, you can provide us with detailed input and output examples in tabular form so that we can better solve the problem for you.

Best Regards,

Neeko Tang

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

[Arwen16]

Hi @Anonymous,

thank you for your answer. I understand your measures. What I forgot to mention is that my expected result is to show in a card a text just stating: "There are no new countries in the last 12 months" if there are not new countries in the last 12 months and nothing otherwise.
Your solution only works if I have a visualization that includes the country column in it.
Thanks for your help!

[amitchandak]

@Arwen16 , refer if this customer retention logic can help

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...

Power BI Items/Categories not sold to the customers
Power BI Items/Categories not sold to the customers: https://youtu.be/AbuKvAnicwo

Check for the new logic

You need to measure like 

Rolling 12 = CALCULATE(Count(Table[Value]),DATESINPERIOD('Date'[Date],MAX('Date'[Date]),-12,MONTH))

if(not(isblank([Rolling 12])), "New", Blank())

[Arwen16]

Hi @amitchandak,

Thank you for your answer!
I will check the links and see if I can adapt them to my needs.

What I forgot to mention is that my expected result is to show in a card a text just stating: "There are no new countries in the last 12 months" if there are not new countries in the last 12 months and nothing otherwise.