Skip to main content
cancel
Turn on suggestions
Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.
Showing results for 
Search instead for 
Did you mean: 

Don't miss out! 2025 Microsoft Fabric Community Conference, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount. Prices go up February 11th. Register now.

Reply
Alice_Cooper
Helper II
Helper II

Calculations based on Selected Date Selected and set Flags

‎11-30-2022 09:10 PM
 
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
Labels:
Message 1 of 38
2,229 Views
0
3 ACCEPTED SOLUTIONS
Alice_Cooper
Helper II
Helper II
In response to MFelix

‎12-09-2022 10:03 AM

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

Message 22 of 38
1,983 Views
0
MFelix
Super User
Super User
In response to Alice_Cooper

‎12-12-2022 01:53 AM

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

MyTestBI (1) (1).pbix
Message 23 of 38
1,953 Views
MFelix
Super User
Super User
In response to Alice_Cooper

‎12-17-2022 03:55 AM

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

MyTestBI Aniversary Version.pbix
Message 34 of 38
1,844 Views
37 REPLIES 37
Alice_Cooper
Helper II
Helper II

‎12-07-2022 07:20 PM

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 
 
Message 17 of 38
1,598 Views
0
MFelix
Super User
Super User
In response to Alice_Cooper

‎12-08-2022 10:45 AM

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



MyTestBI (1).pbix
Message 18 of 38
1,584 Views
0
Alice_Cooper
Helper II
Helper II
In response to MFelix

‎12-13-2022 09:44 PM

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

Message 28 of 38
1,484 Views
0
MFelix
Super User
Super User
In response to Alice_Cooper

‎12-15-2022 02:02 AM

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



16 KB
MyTestBI 13-12-22 (1).pbix
Message 30 of 38
1,466 Views
0
Alice_Cooper
Helper II
Helper II
In response to MFelix

‎12-15-2022 03:21 PM

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? 

 

 

Message 31 of 38
1,465 Views
0
Alice_Cooper
Helper II
Helper II
In response to Alice_Cooper

‎12-17-2022 01:18 AM

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

 

 

 

Message 33 of 38
1,435 Views
0
MFelix
Super User
Super User
In response to Alice_Cooper

‎12-17-2022 03:55 AM

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



MyTestBI Aniversary Version.pbix
Message 34 of 38
1,845 Views
Alice_Cooper
Helper II
Helper II
In response to MFelix

‎12-18-2022 05:07 PM

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

Message 36 of 38
1,390 Views
0
MFelix
Super User
Super User
In response to Alice_Cooper

‎12-19-2022 06:19 AM

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



Message 37 of 38
1,368 Views
0
Alice_Cooper
Helper II
Helper II
In response to MFelix

‎12-19-2022 11:43 AM

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

Message 38 of 38
1,360 Views
0
Alice_Cooper
Helper II
Helper II
In response to MFelix

‎12-17-2022 01:32 PM

Thank you so much...

 

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

Message 35 of 38
1,413 Views
0
MFelix
Super User
Super User
In response to Alice_Cooper

‎12-16-2022 05:14 AM

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



Message 32 of 38
1,450 Views
0
MFelix
Super User
Super User
In response to Alice_Cooper

‎12-14-2022 05:51 AM

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



Message 29 of 38
1,470 Views
0
Alice_Cooper
Helper II
Helper II
In response to MFelix

‎12-08-2022 03:00 PM

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 )

 

 

 

Message 19 of 38
1,580 Views
0
Alice_Cooper
Helper II
Helper II
In response to Alice_Cooper

‎12-08-2022 07:49 PM

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

 

 

 

Message 20 of 38
1,567 Views
0
MFelix
Super User
Super User
In response to Alice_Cooper

‎12-09-2022 08:15 AM

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



Message 21 of 38
1,575 Views
0
Alice_Cooper
Helper II
Helper II
In response to MFelix

‎12-09-2022 10:03 AM

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 🙂

 

 

 

 

Message 22 of 38
1,984 Views
0
MFelix
Super User
Super User
In response to Alice_Cooper

‎12-12-2022 01:53 AM

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



MyTestBI (1) (1).pbix
Message 23 of 38
1,954 Views
Alice_Cooper
Helper II
Helper II
In response to MFelix

‎12-12-2022 02:05 AM

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?

 

 

Message 24 of 38
1,528 Views
0
MFelix
Super User
Super User
In response to Alice_Cooper

‎12-12-2022 02:15 AM

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



Message 25 of 38
1,524 Views
0

Helpful resources

Announcements
Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Prices go up Feb. 11th.

Jan25PBI_Carousel

Power BI Monthly Update - January 2025

Check out the January 2025 Power BI update to learn about new features in Reporting, Modeling, and Data Connectivity.

Jan NL Carousel

Fabric Community Update - January 2025

Find out what's new and trending in the Fabric community.

View All