Forum Discussion
Helper IICalculations 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 | EOMLeaseEndDate | FinYearLeaseEndDate | CalYearLeaseEndDate | PropertyID | Suburb | Result1 | Result2 | Result3 | Result4 | Result5 | Result6 |
| 31/07/2022 | 21/06/2022 | 30/06/2022 | 2022 | 2022 | 30/07/2022 | 31/07/2022 | 2023 | 2022 | 1 | BUNDALL | 0 | 0 | 0 | 1 | 1 | 0 |
| 31/07/2022 | 2/07/2022 | 30/06/2022 | 2022 | 2022 | 15/01/2023 | 31/01/2023 | 2023 | 2023 | 2 | BUNDALL | 0 | 0 | 0 | 1 | 1 | 0 |
| 31/07/2022 | 31/07/2022 | 31/07/2022 | 2023 | 2022 | 10/01/2023 | 31/01/2023 | 2023 | 2023 | 3 | BUNDALL | 1 | 0 | 1 | 0 | 1 | 0 |
| 31/07/2022 | 7/07/2022 | 31/07/2022 | 2023 | 2022 | 7/07/2023 | 31/07/2023 | 2024 | 2023 | 6 | SURFERS PARADISE | 1 | 0 | 1 | 0 | 1 | 0 |
| 31/07/2022 | 5/07/2022 | 31/07/2022 | 2023 | 2022 | 1/07/2025 | 31/07/2025 | 2026 | 2025 | 34 | SURFERS PARADISE | 1 | 0 | 1 | 0 | 1 | 0 |
| 31/08/2022 | 2/08/2022 | 31/08/2022 | 2023 | 2022 | 2/08/2023 | 31/08/2023 | 2024 | 2023 | 6 | SPRING HILL | 0 | 0 | 0 | 0 | 0 | 0 |
| 31/08/2022 | 5/08/2022 | 31/08/2022 | 2023 | 2022 | 5/08/2023 | 31/08/2023 | 2024 | 2023 | 8 | NEW FARM | 1 | 0 | 1 | 0 | 1 | 0 |
| 30/09/2022 | 1/09/2022 | 30/09/2022 | 2023 | 2022 | 1/09/2023 | 30/09/2023 | 2024 | 2023 | 9 | NORTH WARD | 1 | 1 | 1 | 0 | 1 | 0 |
| 30/09/2022 | 9/09/2022 | 30/09/2022 | 2023 | 2022 | 9/09/2023 | 30/09/2023 | 2024 | 2023 | 13 | SMITHFIELD | 1 | 1 | 1 | 0 | 1 | 0 |
| 30/09/2022 | 15/09/2022 | 30/09/2022 | 2023 | 2022 | 15/09/2023 | 30/09/2023 | 2024 | 2023 | 14 | RAILWAY ESTATE | 1 | 1 | 1 | 0 | 1 | 0 |
| 31/07/2023 | 3/07/2023 | 31/07/2023 | 2024 | 2023 | 3/01/2024 | 31/01/2024 | 2024 | 2024 | 18 | INALA | 0 | 0 | 0 | 0 | 0 | 0 |
| 31/07/2023 | 4/07/2023 | 31/07/2023 | 2024 | 2023 | 4/01/2024 | 31/01/2024 | 2024 | 2024 | 20 | RAILWAY ESTATE | 0 | 0 | 0 | 0 | 0 | 0 |
| 31/07/2023 | 2/07/2023 | 31/07/2023 | 2024 | 2023 | 2/07/2024 | 31/07/2024 | 2025 | 2024 | 31 | CAPALABA | 0 | 0 | 0 | 0 | 0 | 0 |
| 31/08/2023 | 29/07/2023 | 31/08/2023 | 2024 | 2023 | 1/02/2024 | 29/02/2024 | 2024 | 2024 | 22 | NORTH WARD | 0 | 0 | 0 | 0 | 0 | 0 |
| 31/08/2023 | 2/08/2023 | 31/08/2023 | 2024 | 2023 | 2/02/2024 | 29/02/2024 | 2024 | 2024 | 23 | RAILWAY ESTATE | 0 | 0 | 0 | 0 | 0 | 0 |
| 30/09/2023 | 1/09/2023 | 30/09/2023 | 2024 | 2023 | 1/03/2024 | 31/03/2024 | 2024 | 2024 | 24 | RAILWAY ESTATE | 0 | 0 | 0 | 0 | 0 | 0 |
| 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 |
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:
37 Replies
- MFelix
Super User
Hi Alice_Cooper
First of all add a table that you use has a slicer with the EOMRP values
Add the following measures:
Earliest properties = CALCULATE ( DISTINCTCOUNT ( Leases[PropertyID] ), FILTER ( ALL ( Leases ), Leases[EOMRPT] <= MAX ( DAteFilter[EOMRPT] ) ) ) Number of properties = CALCULATE ( DISTINCTCOUNT ( Leases[PropertyID] ), Leases[EOMRPT] = MAX ( DAteFilter[EOMRPT] ) ) Properties PY YTD = VAR PYYTD = CALCULATE ( DISTINCTCOUNT ( Leases[PropertyID] ), FILTER ( ALL ( Leases ), Leases[EOMRPT] >= MINX ( FILTER ( ALL ( Leases ), Leases[FinYearLeaseStartDate] = LOOKUPVALUE ( Leases[FinYearLeaseStartDate], Leases[EOMRPT], MAX ( DAteFilter[EOMRPT] ) - 1 ) ), Leases[EOMRPT] ) && Leases[EOMRPT] <= MAX ( DAteFilter[EOMRPT] ) && Leases[FinYearLeaseStartDate] = LOOKUPVALUE ( Leases[FinYearLeaseStartDate], Leases[EOMRPT], MAX ( DAteFilter[EOMRPT] ) ) - 1 ) ) RETURN IF ( NOT ( ISBLANK ( PYYTD ) ), PYYTD, "N/A" ) Properties PY YTD cal year = VAR PYYTD = CALCULATE ( DISTINCTCOUNT ( Leases[PropertyID] ), FILTER ( ALL ( Leases ), Leases[EOMRPT] >= MINX ( FILTER ( ALL ( Leases ), Leases[CalYearLeaseStartDate] = LOOKUPVALUE ( Leases[CalYearLeaseStartDate], Leases[EOMRPT], MAX ( DAteFilter[EOMRPT] ) ) ), Leases[EOMRPT] ) - 1 && Leases[EOMRPT] <= MAX ( DAteFilter[EOMRPT] ) && Leases[CalYearLeaseStartDate] = LOOKUPVALUE ( Leases[CalYearLeaseStartDate], Leases[EOMRPT], MAX ( DAteFilter[EOMRPT] ) - 1 ) ) ) RETURN IF ( NOT ( ISBLANK ( PYYTD ) ), PYYTD, "N/A" ) Properties YTD = CALCULATE ( DISTINCTCOUNT ( Leases[PropertyID] ), FILTER ( ALL ( Leases ), Leases[EOMRPT] >= MINX ( FILTER ( ALL ( Leases ), Leases[FinYearLeaseStartDate] = LOOKUPVALUE ( Leases[FinYearLeaseStartDate], Leases[EOMRPT], MAX ( DAteFilter[EOMRPT] ) ) ), Leases[EOMRPT] ) && Leases[EOMRPT] <= MAX ( DAteFilter[EOMRPT] ) && Leases[FinYearLeaseStartDate] = LOOKUPVALUE ( Leases[FinYearLeaseStartDate], Leases[EOMRPT], MAX ( DAteFilter[EOMRPT] ) ) ) ) Properties YTD calendar yaer = CALCULATE ( DISTINCTCOUNT ( Leases[PropertyID] ), FILTER ( ALL ( Leases ), Leases[EOMRPT] >= MINX ( FILTER ( ALL ( Leases ), Leases[CalYearLeaseStartDate] = LOOKUPVALUE ( Leases[CalYearLeaseStartDate], Leases[EOMRPT], MAX ( DAteFilter[EOMRPT] ) ) ), Leases[EOMRPT] ) && Leases[EOMRPT] <= MAX ( DAteFilter[EOMRPT] ) && Leases[CalYearLeaseStartDate] = LOOKUPVALUE ( Leases[CalYearLeaseStartDate], Leases[EOMRPT], MAX ( DAteFilter[EOMRPT] ) ) ) )Result below:
I only have questions about your last remark about the flag what do you need to get the result?Is the one that appears on the columns resut one to six?
- Alice_Cooper
Thank you Miguel,
I will try out your solution later today ...
In terms of Results 1 to 6
I need a Flag for records that:
match condition a. (Result 1)
match condition b. (Result 2)
match condition c. (Result 3)
match condition d. (Result 4)
match condition e. (Result 5)
match condition f. (Result 6)
What I put in the table sis the result I expect ...
The flags will be used to do further calculations, without the need for complex Dax ... for example
Result3 (Condition c. Properties Financial Year To Date) and
Result4 (Condition d. Properties for previous Financial Year)
I could calculated (using the Flags) (sum(Result3)/sum(Result4))*100 to get %
I would also like the flags to be able to quickly pull graphs together ...
I hope thsi clarifies ...
Are you able to show me how to returmn the value of 1 when the conditions in (a) through (f) are met?
Thank you
- Alice_Cooper
Ola Miguel,
Esperamos que of final da Copa Mundial seja cheio de alegria para Brazil o Portugal?
Munito obrigado pelo Ficheiro ...
Voce tem oportunidade para me a judar con a segunda parte do problema?
The Flags ....? Results1 a Result6 ...
Munito Obrigado....
- Alice_Cooper
Ola Miguel,
Eu so Brasiliero moro aqui na Australia ... Muinto Obrigado ...
Voce Poderia me mandar o ficheiro (file) de Power BI ..? Desculpa o portugues estao morand en Australia masi de 40 anos ...
Abraco..... Vai Brazil Copa 2022
- MFelix
- JoaoMarcelino
Responsive Resident
Brilliant! 😲
- Alice_Cooper
Miguel,
I have a problem. My data is as follows:
The (Effective Lease End Dates) for EOMRPT 30/11/2022 falls in two calendar years 2024 & 2023 these are valid.
The same is for EOMRPT of 31/03/23 & 31/05/23
EOMRPT
Lease Start Date
Effective Lease End Date
CalYearLeaseEndDate
31/07/2022
7/07/2022
30/06/2023
2023
31/07/2022
10/07/2022
10/01/2023
2023
31/08/2022
2/08/2022
31/07/2023
2023
31/08/2022
5/08/2022
5/08/2023
2023
30/09/2022
1/09/2022
1/09/2023
2023
30/09/2022
9/09/2022
9/09/2023
2023
30/09/2022
15/09/2022
15/09/2023
2023
30/11/2022
11/11/2022
11/05/2024
2024
30/11/2022
22/11/2022
22/05/2023
2023
31/01/2023
4/01/2023
4/01/2024
2024
31/01/2023
5/01/2023
5/07/2023
2023
31/01/2023
10/01/2023
10/01/2024
2024
28/02/2023
2/02/2023
2/08/2024
2024
28/02/2023
4/02/2023
4/08/2024
2024
31/03/2023
1/03/2023
1/09/2023
2023
31/03/2023
2/03/2023
2/09/2023
2023
31/03/2023
4/03/2023
4/09/2023
2023
31/03/2023
5/03/2023
5/03/2024
2024
30/04/2023
1/04/2023
1/04/2024
2024
31/05/2023
2/05/2023
2/05/2024
2024
31/05/2023
4/05/2023
4/05/2024
2024
31/05/2023
5/05/2023
5/11/2023
2023
30/06/2023
1/06/2023
1/12/2023
2023
30/06/2023
2/06/2023
2/12/2023
2023
31/07/2023
2/07/2023
2/07/2024
2024
31/07/2023
3/07/2023
3/01/2024
2024
31/07/2023
4/07/2023
4/01/2024
2024
31/08/2023
1/08/2023
1/02/2024
2024
31/08/2023
2/08/2023
2/02/2024
2024
30/09/2023
1/09/2023
1/03/2024
2024
The code :
Out-Properties with Lease End Date in Calendar Year = CALCULATE (
DISTINCTCOUNT ( Leases[Property ID] ),
FILTER (
ALL ( Leases ),
Leases[EOMRPT]
>= MINX (
FILTER (
ALL ( Leases ),
Leases[CalYearLeaseEndDate]
= LOOKUPVALUE (
Leases[CalYearLeaseEndDate],
Leases[EOMRPT], MAX ( EOMRPT[EOMRPT] )
)
),
Leases[EOMRPT]
)
&& year(Leases[EOMRPT]) <= MAX (EOMRPT[EOMRPT] )
&& Leases[CalYearLeaseEndDate]
= LOOKUPVALUE (
Leases[CalYearLeaseEndDate],
Leases[EOMRPT], MAX ( EOMRPT[EOMRPT])
)
)
)
Error Message:
MdxScript(Model) (252, 21) Calculation error in measure 'Leases'[Out-Properties with Lease End Date in Calendar Year]: A table of multiple values was supplied where a single value was expected.
But the data is correct …
If we select EOMRPT as 30/11/2022 we want to test if the CalYearLeaseEndDate is equal to 2022 in this case, it should return Zero.
If If we select EOMRPT as 31/03/2023 we want to test if the CalYearLeaseEndDate is equal to 2023 in this case, it should return three.
Is there a way for this to work?
- Alice_Cooper
This is what I need:
If I select the EOMRPT as 31/03/23 I want to be able to count how many leases started in the calendar year and how many ended in the calendar year.
I should get 9 Started
(IN-Properties with Lease Start Date in Calendar Year)
and 12 Ended
(Out-Properties with Lease End Date in Calendar Year)
…. Therefore, I can say that I have 3 (12-9) active leases…
Bal-Active Leases During Calendar Year = (Out-Properties with Lease End Date in Calendar Year)- (Out-Properties with Lease End Date in Calendar Year)
- Alice_Cooper
Miguel,
Here is a link to the POWER BI that I have been working on ... when I select 30/11/22, 31/01/23, 31/03/23, 31/05/23 or 31/01/23 I get the
Error Message:
MdxScript(Model) (50, 21) Calculation error in measure 'Leases'[Properties YTD]: A table of multiple values was supplied where a single value was expected.The data is correct as I can have multiple leases with different Start and End Date for each EOMRPT ...
Not sure how to fix ... I have been trying but cannot work it out ... as it will not allow me to have expressions using MAX???
THE LINK : MyTestBI
- Alice_Cooper
My Power BI file can be fount at this link: https://drive.google.com/file/d/199JevChzwhNhGU1zxHVUNo4pbfMYng4F/view?usp=share_link
My data is as follows:
The (Effective Lease End Dates) for EOMRPT 30/11/2022 falls in two calendar years 2024 & 2023 these are valid.
The same is for EOMRPT of 31/03/23 & 31/05/23
EOMRPT
Lease Start Date
Effective Lease End Date
CalYearLeaseEndDate
31/07/2022
7/07/2022
30/06/2023
2023
31/07/2022
10/07/2022
10/01/2023
2023
31/08/2022
2/08/2022
31/07/2023
2023
31/08/2022
5/08/2022
5/08/2023
2023
30/09/2022
1/09/2022
1/09/2023
2023
30/09/2022
9/09/2022
9/09/2023
2023
30/09/2022
15/09/2022
15/09/2023
2023
30/11/2022
11/11/2022
11/05/2024
2024
30/11/2022
22/11/2022
22/05/2023
2023
31/01/2023
4/01/2023
4/01/2024
2024
31/01/2023
5/01/2023
5/07/2023
2023
31/01/2023
10/01/2023
10/01/2024
2024
28/02/2023
2/02/2023
2/08/2024
2024
28/02/2023
4/02/2023
4/08/2024
2024
31/03/2023
1/03/2023
1/09/2023
2023
31/03/2023
2/03/2023
2/09/2023
2023
31/03/2023
4/03/2023
4/09/2023
2023
31/03/2023
5/03/2023
5/03/2024
2024
30/04/2023
1/04/2023
1/04/2024
2024
31/05/2023
2/05/2023
2/05/2024
2024
31/05/2023
4/05/2023
4/05/2024
2024
31/05/2023
5/05/2023
5/11/2023
2023
30/06/2023
1/06/2023
1/12/2023
2023
30/06/2023
2/06/2023
2/12/2023
2023
31/07/2023
2/07/2023
2/07/2024
2024
31/07/2023
3/07/2023
3/01/2024
2024
31/07/2023
4/07/2023
4/01/2024
2024
31/08/2023
1/08/2023
1/02/2024
2024
31/08/2023
2/08/2023
2/02/2024
2024
30/09/2023
1/09/2023
1/03/2024
2024
The code :
Out-Properties with Lease End Date in Calendar Year = CALCULATE (
DISTINCTCOUNT ( Leases[Property ID] ),
FILTER (
ALL ( Leases ),
Leases[EOMRPT]
>= MINX (
FILTER (
ALL ( Leases ),
Leases[CalYearLeaseEndDate]
= LOOKUPVALUE (
Leases[CalYearLeaseEndDate],
Leases[EOMRPT], MAX ( EOMRPT[EOMRPT] )
)
),
Leases[EOMRPT]
)
&& year(Leases[EOMRPT]) <= MAX (EOMRPT[EOMRPT] )
&& Leases[CalYearLeaseEndDate]
= LOOKUPVALUE (
Leases[CalYearLeaseEndDate],
Leases[EOMRPT], MAX ( EOMRPT[EOMRPT])
)
)
)
Error Message:
MdxScript(Model) (252, 21) Calculation error in measure 'Leases'[Out-Properties with Lease End Date in Calendar Year]: A table of multiple values was supplied where a single value was expected.
But the data is correct …
If we select EOMRPT as 30/11/2022 we want to test if the CalYearLeaseEndDate is equal to 2022 in this case, it should return Zero.
If If we select EOMRPT as 31/03/2023 we want to test if the CalYearLeaseEndDate is equal to 2023 in this case, it should return three.
OK Miguel.
This is what I need:
If I select the EOMRPT as 31/03/23 I want to be able to count how many leases started in the calendar year and how many ended in the calendar year.
I should get 9 Started
(IN-Properties with Lease Start Date in Calendar Year)
and 12 Ended
(Out-Properties with Lease End Date in Calendar Year)
…. Therefore, I can say that I have 3 (12-9) active leases…
Bal-Active Leases During Calendar Year = (Out-Properties with Lease End Date in Calendar Year)- (Out-Properties with Lease End Date in Calendar Year)
- MFelix
Hi Alice_Cooper ,
The file is giving an error when trying to download it can you check the link you provided?
- Alice_Cooper
My appologies ... Here it is ...MyTestBi
Jogao do Portugal ... foi maravilhoso .... Meu avo, infelizmente falecido, era de Lisboa ... Vamos Portugal...
- Alice_Cooper
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))RETURNIF ( NOT ( ISBLANK ( ODEOMRPT ) ), ODEOMRPT, 0 )The file is here .... MyPROBLEM.PBIX- MFelix
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.
- Alice_Cooper
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]))))RETURNIF ( NOT ( ISBLANK ( OPFYYTD ) ), OPFYYTD, 0 )
