Filtering by region/town on top of date

[ValentinBIA]

Good morning Power BI community!

I'm running into some problem building my model, and I was wondering is you guys could help.

I want to have a Card that gives me the average number of rentals per station.

However, the number of stations that I have is not fixed. It increased over time.

Moreover, I want to be able to filter by region/town on top of date filtering.

Station_ID	Opening date	City	Region
A	1/1/2018	Winterfell	North
B	1/1/2018	Whiterun	North
C	2/1/2018	Hogwart	South
D	4/1/2018	Hogwart	South
E	4/1/2018	Whiterun	North
F	5/1/2018	Winterfell	North

And here is the rentals table:

Rental_ID	Date
1	5/1/2018
2	9/1/2018
3	20/1/2018
4	20/1/2018
5	22/1/2018
6	29/1/2018
7	2/2/2018
8	5/2/2018
9	5/2/2018
10	25/2/2018
11	9/4/2018
12	11/4/2018
13	21/4/2018
14	3/5/2018
15	13/5/2018
16	15/5/2018
17	15/5/2018
18	19/5/2018
19	25/5/2018
20	25/5/2018

I already use this measure to filter by date, but filtering by town/region does not seem to work correctly.

Measure =
SUMX (
    SUMMARIZE ( 'Calendar', 'Calendar'[Date].[Year], 'Calendar'[Date].[Month] ),
    CALCULATE (
        DIVIDE (
            COUNTROWS ( Rentals ),
            COUNTROWS (
                FILTER (
                    ALL ( Stations ),
                    Stations[Opening date] <= MAX ( 'Calendar'[Date].[Date] )
                )
            )
        )
    )
)

Can anyone help me with that?

Thank you very much!

[v-cherch-msft]

Hi @ValentinBIA

You may refer to below measure:

Measure =
SUMX (
    SUMMARIZE ( 'calendar', 'Calendar'[Date].[Year], 'Calendar'[Date].[Month] ),
    CALCULATE (
        DIVIDE (
            COUNTROWS ( Rentals ),
            CALCULATE (
                COUNTROWS ( Stations ),
                FILTER (
                    ALLEXCEPT ( Stations, Stations[Region] ),
                    MONTH ( Stations[Opening date] ) <= MONTH ( MAX ( Stations[Opening date] ) )
                        && Stations[Opening date] <= MAX ( 'calendar'[Date].[Date] )
                )
            )
        )
    )
)

Regards,

Cherie