Forum Discussion

Alice_Cooper's avatar
Alice_Cooper
Icon for Helper II rankHelper II
3 years ago
Solved

Calculations based on Selected Date Selected and set Flags

  Desperately need help -- been trying for days but can't crack it …    EOMRPT LeaseStartDate EOMLeaseStartDate FinYearLeaseStartDate CalYearLeaseStartDate LeaseEndDate EOMLeaseEndD...
Need Help
  • Alice_Cooper's avatar
    Alice_Cooper
    3 years ago

    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 🙂

     

     

     

     

  • MFelix's avatar
    MFelix
    3 years ago

    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 )
  • MFelix's avatar
    MFelix
    3 years ago

    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:

     

     

MFelix's avatar
MFelix
Icon for Super User rankSuper User
3 years ago

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:

 

 

Alice_Cooper's avatar
Alice_Cooper
Icon for Helper II rankHelper II
3 years ago

Miguel, I am trying to get average rent breakdown, but it does not seem to be working?

 

I am very confused as to why? I obviously need a measure? Would you be able to help me and also put some comments in the measure so I can understand how it works? Thank you

 

Here is the file its the Map Active Tab

  • MFelix's avatar
    MFelix
    Icon for Super User rankSuper User
    3 years ago

    Hi  Alice_Cooper ,

     

    When you refer the average rent breakdown what is the value you need?

     

    The total value of the ones that are active? You can try the following metric.

     

    Average Rent = 
//Table to pick up the active headleases
VAR temp_table =
    CALCULATETABLE (
        FILTER (
            ADDCOLUMNS ( Leases, "ActiveHeadleases", [Active Headleases Count] ),
            // Filter of active headleases
            [ActiveHeadleases] > 0
        ),
        // Turn off the date dimension because of the leases table don't get impact by the slicer
        CROSSFILTER ( DimDate[Date], Leases[EOMRPT], NONE )
    )
RETURN
    DIVIDE (
        // Total Values of leases by location/type
        SUMX ( temp_table, Leases[Weekly Market Rent] ),
        // Active headleases to give overal value
        [Active Headleases Count]
    )

    If this is not the result you need please tell me.

     

  • Alice_Cooper's avatar
    Alice_Cooper
    Icon for Helper II rankHelper II
    3 years ago

    Thank you Miguel, exactly what I wanted ... and thank you for putting in the comments ... this helps me understand the logic of how it works...