Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

Reply
Alice_Cooper
Helper II
Helper II

Calculations based on Selected Date Selected and set Flags

 
Desperately need help -- been trying for days but can't crack it … 
 
EOMRPTLeaseStartDateEOMLeaseStartDateFinYearLeaseStartDateCalYearLeaseStartDateLeaseEndDateEOMLeaseEndDateFinYearLeaseEndDateCalYearLeaseEndDatePropertyIDSuburbResult1Result2Result3Result4Result5Result6
31/07/202221/06/202230/06/20222022202230/07/202231/07/2022202320221BUNDALL000110
31/07/20222/07/202230/06/20222022202215/01/202331/01/2023202320232BUNDALL000110
31/07/202231/07/202231/07/20222023202210/01/202331/01/2023202320233BUNDALL101010
31/07/20227/07/202231/07/2022202320227/07/202331/07/2023202420236SURFERS PARADISE101010
31/07/20225/07/202231/07/2022202320221/07/202531/07/20252026202534SURFERS PARADISE101010
31/08/20222/08/202231/08/2022202320222/08/202331/08/2023202420236SPRING HILL000000
31/08/20225/08/202231/08/2022202320225/08/202331/08/2023202420238NEW FARM101010
30/09/20221/09/202230/09/2022202320221/09/202330/09/2023202420239NORTH WARD111010
30/09/20229/09/202230/09/2022202320229/09/202330/09/20232024202313SMITHFIELD111010
30/09/202215/09/202230/09/20222023202215/09/202330/09/20232024202314RAILWAY ESTATE111010
31/07/20233/07/202331/07/2023202420233/01/202431/01/20242024202418INALA000000
31/07/20234/07/202331/07/2023202420234/01/202431/01/20242024202420RAILWAY ESTATE000000
31/07/20232/07/202331/07/2023202420232/07/202431/07/20242025202431CAPALABA000000
31/08/202329/07/202331/08/2023202420231/02/202429/02/20242024202422NORTH WARD000000
31/08/20232/08/202331/08/2023202420232/02/202429/02/20242024202423RAILWAY ESTATE000000
30/09/20231/09/202330/09/2023202420231/03/202431/03/20242024202424RAILWAY ESTATE000000
 
 
What I Need:
 
1. Filter so User Can Select EOMRPT
2. Based on this SELECTEDVALUE … lets call this NM-SEOMRPT and lets select 30/09/22
 
A Table that returns
 
a. Number Of Properties (Sum Distinct Property ID) from earliest EOMRPT to NM-SEOMRPT
b. Number of Properties (Sum Distinct Property ID) during NM-SEOMRPT
c. Number of Properties (Sum Distinct Property ID) for Financial Year To Date (Based on FinYearLeaseStartDate = the financial year of NM-SEOMRPT)
d. Number of Properties (Sum Distinct Property ID) for previous Financial Year (Based on FinYearLeaseStartDate = the financial year of NM-SEOMRPT), if no value return "N/A"
e. Number of Properties (Sum Distinct Property ID) for Calendar Year To Date (Based on CalYearLeaseStartDate = the financial year of NM-SEOMRPT)
f. Number  of Properties (Sum Distinct Property ID) for previous Calendar Year (Based on CalYearLeaseStartDate= the financial year of NM-SEOMRPT), if no value return "N/A"
 
Example User Selects 30/09/22 from EOMRPT so Selectedvalue for NM-SEOMRPT evaluates to = 30/09/22
 
I should get (see Check Table) for:
a.    9 Properties from earliest EOMRPT to 30/09/22 …... One Property ID 6 is a duplicate
b.    3 Properties during 30/09/22
c.    7 Properties Financial Year To Date (based on FinYearLeaseStartDate = 2023 financial year for 30/09/22 NM-SEOMRPT) …. One Property ID 6 is a duplicate
d.    2 Properties for previous Financial Year 2022
e.    9 Properties for Calendar Year To Date (Based on CalYearLeaseStartDate = the calendar year for 30/09/22 NM-SEOMRPT)  …... One Property ID 6 is a duplicate
f.    N/A Properties for previous Calendar Year (Based on CalYearLeaseStartDate= the calendar year of 30/09/22 NM-SEOMRPT)
 
I would like to flag each record that results in a match for the above conditions (Result1 ... Result6) with a value of 1 if it returns a value for the condition else 0. I will use this to graph and do other analysis
3 ACCEPTED SOLUTIONS

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 🙂

 

 

 

 

View solution in original post

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 )

Regards

Miguel Félix


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog: Power BI em Português



View solution in original post

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 )
        )
    )

 

MFelix_0-1671278097275.png

Using the new measure has bubble size you get:

 

MFelix_1-1671278134395.png

 


Regards

Miguel Félix


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog: Power BI em Português



View solution in original post

37 REPLIES 37
Alice_Cooper
Helper II
Helper II

Felix... 

 

I have a problem with this one as well:

 

Out-Properties with Lease End Date during period =
VAR ODEOMRPT =
CALCULATE (
    DISTINCTCOUNT ( Leases[Property ID] ),
    DATESINPERIOD(Leases[Effective Lease End Date], MAX ( EOMRPT[EOMRPT] ),-30,DAY)
)
RETURN
    IF ( NOT ( ISBLANK ( ODEOMRPT ) ), ODEOMRPT, 0 )
 
The file is here .... MyPROBLEM.PBIX 
 

Hi @Alice_Cooper ,

 

Regarding the Out-Properties with Lease End Date in Calendar Year the issue is the same of the previous one since there is more than one value for the end calendar year, I understand why you have multiple calendar years, because of the length of the leases.

 

For this you need to redo the metric to: 

Out-Properties with Lease End Date in Calendar Year = 
Var OCY  =
CALCULATE (
    DISTINCTCOUNT ( Leases[Property ID] ),
    FILTER (
        ALL ( Leases ),
        Leases[EOMRPT]
            >= MINX (
                FILTER (
                    ALL ( Leases ),
                    Leases[CalYearLeaseEndDate]
                        = YEAR( MAX ( EOMRPT[EOMRPT] )
                        )
                ),
                Leases[EOMRPT]
            )
            && year(Leases[EOMRPT]) <= MAX (EOMRPT[EOMRPT] )
            && Leases[CalYearLeaseEndDate]
                = Year (MAX( EOMRPT[EOMRPT])) 
                
    )
)
    RETURN
        IF ( NOT ( ISBLANK ( OCY ) ), OCY , 0 )

 

Regarding the measure Out-Properties with Lease End Date during period redo the measure to:

 

Out-Properties with Lease End Date during period = 
VAR ODEOMRPT =
CALCULATE (
    DISTINCTCOUNT ( Leases[Property ID] ),
   ALL(Leases), 
   DATESINPERIOD(Leases[Effective Lease End Date], date(year(MAX ( EOMRPT[EOMRPT] )), MONTH(MAX(EOMRPT[EOMRPT])),1), 1, MONTH)
)
RETURN
    IF ( NOT ( ISBLANK ( ODEOMRPT ) ), ODEOMRPT, 0 )

 

Concerning the flags, can you explain what you want to sahow on the charts? because using the measures that were created and the EOMRP table you can do the charts has you need.

 

 


Regards

Miguel Félix


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog: Power BI em Português



Hello Felix... This is where I think I need the flags ...

 

I am now trying to Map Only Active Headlease Properties but I am unable to map as Lat/Log do not show up as "don't sumarise"...

 

Also the $ value of calculated Total Rental for All active propoerties, based on select seems wrong ...

 

Here is the link to the file ... that has this issue

 

Your help is appreciated ...

 

PowerBI MyTestBI DBay Version

Hi @Alice_Cooper ,

 

You have some values that are not matching in the excel:

  • Values for the Financial Year are not the same that are in Power BI:

MFelix_0-1671097293702.pngMFelix_1-1671097340771.png

  • On your excel spreadsheet you also didn't consider all values 

I have updated the excel file and the new calculations are has below:

MFelix_2-1671097391439.png

I have changed almost all metrics since the new calendar table allows to simplify the measures check result below and in attach files:

MFelix_3-1671098474193.png

 

MFelix_4-1671098484717.png

 

 

 

 

 


Regards

Miguel Félix


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog: Power BI em Português



Thank you very much Miguel,

 

Its been a steep learning curve for me and I thank you for your help.

 

Could I ask one more question please?

(See PowerBI LInk ) ... I am trying to Map only Active Headleases but when using Lat/Long the option of "Don't Sumarize" has gone ... I assume its because of the filtering? 

 

 

Thank You Felix,

 

But it is not quite working .. it does not map the Active Headleases ... I have also tried to do a XTab for Active Headleases but it only counts the Headleases during the Report Perriod..

 

Look Here POWERBIFILE ... could you take a look and let me know what I am doing wrong please?

 

I really appreciate you help ... it has been really helpful ...

 

 

 

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 )
        )
    )

 

MFelix_0-1671278097275.png

Using the new measure has bubble size you get:

 

MFelix_1-1671278134395.png

 


Regards

Miguel Félix


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog: Power BI em Português



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

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]
    )

MFelix_0-1671459497116.png

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

 


Regards

Miguel Félix


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog: Power BI em Português



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... 

Thank you so much...

 

You are a Star ... I really appreciate it .... Feliz Natal e um Prospero Anno Novo ... Abracao

Hi @Alice_Cooper ,

 

Instead of using the filled map use the map visualization set the lat and long to don't summarize:

MFelix_1-1671196433211.png

 

 


Regards

Miguel Félix


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog: Power BI em Português



Hi @Alice_Cooper ,

 

I have not forgetten you but this last days have been tricky will try to look at this today.


Regards

Miguel Félix


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog: Power BI em Português



Thank you Miguel,

 

It worked ...

Could you help me with the Financial Year One?

 

I need to get the Financial year from EOMRPT and then get the leases ...

 

Try Selecting July 2023 in the PowerBi and we will get errors as the code is using lookup... 

 

It seems that you cant use 

 

I tried using:  

  = ENDOFYEAR( MAX ( EOMRPT[EOMRPT] )
 
But it returns the error message " The first argument to 'ENDOFYEAR' must specify a column."
 
it seems that "
  • This function is not supported for use in DirectQuery mode when used in calculated columns or row-level security (RLS) rules."

Is there anotherway?

 

 

 

ORIGINAL CODE:

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

 

 

 

I need Help with both Financial Year Measures in the MyTest.Pbix file

 

Out-Properties with Lease End Date as at Financial Year To Date
Out-Properties with Lease End Date in Previous Financial Year

 

Once there are in place, I agree I will not need Flags ...

 

Thank you in anticipation ... I have been trying to solve it my self but a bit too complicated ... for me....

 

 

 

Hi @Alice_Cooper ,

 

The issue on this two metrics is the end of the lease date since you can have more than one year how do you select the year that you should consider ?

 

MFelix_0-1670602488228.png

 

In the example above do you consider 2024 or 2025 or both? be aware that we are selected calendar year 2023.


Regards

Miguel Félix


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog: Power BI em Português



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 )

Regards

Miguel Félix


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog: Power BI em Português



Thank you Very Much Felix ... It is now ALL WORKING ...

 

I noticed that I did not use a propoer Date Dimension Table ...

 

Woudl It be a big ask if I reworked my Date Table and if I come into problem ask you to take a look?

 

 

Sure no problem.


Regards

Miguel Félix


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog: Power BI em Português



Helpful resources

Announcements
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.

Power BI Header Change

Power BI Header Update

Read about the exciting updates for the Power BI forum.