Count different cities according to number of users and dates

[Syndicate_Admin]

Very good! Let's see if you can help me because I can't exactly square the calculations I want to do.

It turns out that I have a table of Visitors, where date (dd/mm/yyyy), country, city of origin and number of users are stored, such that:

Date	Country	City	NumUsuarios
1/1/2021	Spain	Madrid	800
1/1/2021	Spain	Barcelona	100
1/1/2021	Germany	Munich	900
1/2/2021	Spain	Madrid	950
1/2/2021	Spain	Barcelona	150
1/2/2021	Germany	Munich	150
1/3/2021	Spain	Madrid	700
1/3/2021	Spain	Barcelona	800
1/3/2021	Germany	Munich	1200
....	...	...	...

I have some filters that allow you to select Country, City, Month and Year, with the peculiarity that if you select the month of March, the calculations are cumulative from January to March. That is, if I have to take out the number of cities that meet the filter of Year=2021 and Month=2 and that have more than 100 users, the result should be "Cities=2" because Madrid and Munich add up to more than 100 users between the months of January and February. If it were the filter of Year=2021 and Month=3 and that they have more than 1000 users, the result would be 3 because already Madrid, Barcelona and Munich exceed the 1000 users enese accumulated from months of January to March.

Be that as it may, I can't square that end result of cities. With the 100 tests I've done, I'm left with the cities of the selected month, instead of the cumulative one, for example. I've tried to make a table with SUMMARIZE where only the data that meets those filters is put in.... in short, always hitting me against a wall.

From so much doing and undoing, in the end I've been left with something like this:

Ciudades = 
var anho = SELECTEDVALUE(Calendar[Fecha].[ Año])
var mes = SELECTEDVALUE('Calendar'[Fecha].[ NroMes])
var fecha = DATESBETWEEN('Calendar'[Fecha], DATE(anho, 1, 1), DATE(anho, mes, 1))

VAR actual =  FILTER(
    ALLEXCEPT('Visitantes', 'Calendar'[Fecha].[ Mes], 'Visitantes'[Pais], 'Visitantes'[Ciudad]),
    'Pasajeros por Escala'[Fecha]>=FIRSTDATE(fecha) && 'Pasajeros por Escala'[Fecha]<=LASTDATE(fecha) && 
    SUM('Pasajeros por Escala'[NumUsuarios])>1000
    )

var valor = calculate(DISTINCTCOUNT('Visitantes'[Ciudad]),actual)

RETURN valor

Let's see if you can give me some light.

Thank you very much in advance.

[Syndicate_Admin · Accepted solution]

In the end I found the solution:

Ciudades = 
var anho = SELECTEDVALUE(Calendar[Fecha].[ Año])
var mes = SELECTEDVALUE('Calendar'[Fecha].[ NroMes])
var fecha = DATESBETWEEN('Calendar'[Fecha], DATE(anho, 1, 1), DATE(anho, mes, 1))

VAR actual =  FILTER(
    ALLEXCEPT('Visitantes', Calendar[Fecha].[ Año],'Visitantes'[País]),
    'Visitantes'[Fecha]>=FIRSTDATE(fecha) && 'Visitantes'[Fecha]<=LASTDATE(fecha)
    )

var tablaResumida= CALCULATETABLE(
    SUMMARIZE('Visitantes', 'Visitantes'[Ciudad], 'Visitantes'[País], "NumUsuarios", SUM('Visitantes'[Pasajeros])),
    actual)
    
RETURN calculate(DISTINCTCOUNT('Visitantes'[Ciudad]),FILTER(tablaResumida, [NumUsuarios]>1000))

[amitchandak]

@Syndicate_Admin , Based on what I got, Try a measure like

countx(filter(summarize(Table, Table[Country], Table[City], "_1", sum(Table[NumUsuarios])), [_1]>1000),[City])

[Syndicate_Admin]

Unfortunately it only takes into account the value marked in the MES filter. In the case of the example, I would return Cities=1 because Munich is the only one in March that meets that it is greater than 1000, when it should be Cities=3 because the sum of January ,February and March of the three cities exceed 1000 users.

The value of the month filter seems to prevail, and does not accumulate them.