topic Re: Calculation if there are new countries in the last 12 months in DAX Commands and Tips

Calculation if there are new countries in the last 12 months

Arwen16 — Fri, 09 Sep 2022 08:33:45 GMT

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

Re: Calculation if there are new countries in the last 12 months

amitchandak — Tue, 06 Sep 2022 13:41:25 GMT

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/ba-p/1361529
Customer Retention Part 2: Period over Period Retention :https://community.powerbi.com/t5/Community-Blog/Customer-Retention-Part-2-Period-over-Period-Retention/ba-p/1377458

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())

Re: Calculation if there are new countries in the last 12 months

Anonymous — Thu, 08 Sep 2022 05:58:36 GMT

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.

Re: Calculation if there are new countries in the last 12 months

Arwen16 — Fri, 09 Sep 2022 08:26:48 GMT

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!

Re: Calculation if there are new countries in the last 12 months

Arwen16 — Fri, 09 Sep 2022 08:32:33 GMT

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.