How to Count New values (haven't appeared in historical months) for the given selections in KPI Block

[abhirup99]

Below is my sample data:

Business_Unit	Product	Time_Period	Country
A	P1	Jan-21	MEX
B	P2	Jan-21	AUS
B	P2	Jan-21	USA
A	P1	Feb-21	USA
A	P2	Feb-21	MEX
B	P3	Feb-21	GER
C	P1	Feb-21	MEX
A	P1	Mar-21	GER
A	P2	Mar-21	USA
A	P3	Mar-21	RUS
A	P3	Mar-21	AUS

Problem Statement explained with examples:

Selections:

Time Period by default = Current Month (Mar-21)

Output in KPI Block Expected:

Current Month New Countries = 1 (which is RUS, as it hasn't appeared in historical months across all the data set)

In addition to the default selection, I also select one Business Unit as 'A'

Selections:

Time Period by default = Current Month (Mar-21) & Business_Unit = 'A'

Output in KPI Block Expected:

Current Month New Countries = 3 (which isGER, RUS & AUS, as for Business_unit = 'A' the two values haven't appeared in historical months)

Similarly, when I select 1 or multiple Products or more than 1 Business_unit, it should give the new countries for that selection

The expression I tried:

Count( distinct (

Aggr(only({<Time_Period = {'Mar-21'}>} distinct Country), Country)

-

Aggr(only({<Time_Period -= {'Mar-21'}>} distinct Country), Country)

))

The above expression is not returning me any value.  Can you please help?

@sunny_talwar , @Gysbert_Wassenaar , @tresesco @OmarBenSalem @lorenzoconforti @hic , @swuehl 

[tresesco · Accepted solution]

First convert your date field from text to date type, like:

Date(Date#(Time_Period, 'MMM-YY'), 'MMM-YY') as Time_Period

Then try expression something like:

=Count(DISTINCT {<Country-={"=Count(Distinct {<Time_Period={`<$(=(Max(Time_Period)))`}>}Country)"}>}Country)