Calculations based on Selected Date Selected and set Flags

Good Point ...

This is what needs to Happen ...

If we select 31/07/23

For (Out-Properties with Lease End Date as at Financial Year To Date)

We need to count the Number of Properties in the Financial Year  to date (EOMRPT) 31/07/23 is Selected  ... so that means all properties with a (Lease End Date) betweem 1/07/23 to 31/07/24 

For (Out-Properties with Lease End Date in Previous Financial Year)

We need to count the Number of Properties in the Previous Year so if (EOMRPT) = 31/07/23 ... it means all properties with a (Lease End Date) betweem 1/07/22 to 30/06/23

BRAZIL OUT ....:(  Vai Portugal 🙂

HI Alice_Cooper ,

try the following codes:

Out-Properties with Lease End Date in Previous Financial Year = 
VAR OPFYYTD =
    CALCULATE (
        DISTINCTCOUNT ( Leases[Property ID] ),
        FILTER (
            
            
            ALL ( Leases ),
            Leases[EOMRPT]
                <= EOMONTH ( MAX ( EOMRPT[EOMRPT] ), 0 ) 
                && Leases[EOMRPT]
                    >= 
                        DATE ( YEAR ( MAX ( EOMRPT[EOMRPT] ) ) - 1, MONTH ( MAX ( EOMRPT[EOMRPT] ) ), 1 )
                       
                    )
        )
    
RETURN
    IF ( NOT ( ISBLANK ( OPFYYTD ) ), OPFYYTD, 0 )


Out-Properties with Lease End Date as at Financial Year To Date =
VAR OATFY =
    CALCULATE (
        DISTINCTCOUNT ( Leases[Property ID] ),
        FILTER (
            ALL ( Leases ),
            Leases[EOMRPT]
                >= EOMONTH ( MAX ( EOMRPT[EOMRPT] ), -1 ) + 1
                && Leases[EOMRPT]
                    <= EOMONTH (
                        DATE ( YEAR ( MAX ( EOMRPT[EOMRPT] ) ) + 1, MONTH ( MAX ( EOMRPT[EOMRPT] ) ), 1 ),
                        0
                    )
        )
    )
RETURN
    IF ( NOT ( ISBLANK ( OATFY ) ), OATFY, 0 )

Hi Alice_Cooper ,

Use the following measure:

Active Houses V1 =
COUNTROWS (
    CALCULATETABLE (
        Leases,
        Leases[Lease Start Date] <= MAX ( DimDate[End of Month] ),
        CROSSFILTER ( DimDate[Date], Leases[EOMRPT], NONE )
    )
)
    - COUNTROWS (
        CALCULATETABLE (
            Leases,
            Leases[Effective Lease End Date] <= MAX ( DimDate[End of Month] ),
            CROSSFILTER ( DimDate[Date], Leases[EOMRPT], NONE )
        )
    )

Using the new measure has bubble size you get: