Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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 |
Solved! Go to Solution.
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
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsHi @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:
Regards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsFelix ... Please See Attached Spreadsheet ...(MyTestCheck.xls) and New Power Bi file ...
The following Measures are not returning the correct result:
In-Properties with Lease End Date as at Financial Year To Date
In-Properties with Lease End Date as at Financial Year To Date
Out-Properties with Lease End Date as at Financial Year To Date
Out-Properties with Lease End Date as at Financial Year To Date
I have tried to get the correct answer but have failed? Could you help me please?
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)
Hi @Alice_Cooper ,
The file is giving an error when trying to download it can you check the link you provided?
Regards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsMy appologies ... Here it is ...MyTestBi
Jogao do Portugal ... foi maravilhoso .... Meu avo, infelizmente falecido, era de Lisboa ... Vamos Portugal...
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
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)
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?
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?
Regards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsBrilliant! 😲
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
Boa tarde @Alice_Cooper ,
Eu sou de Portugal.
Segue o ficheiro em anexo tal como pedido.
Regards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsThank you Miguel,
I notice that this error on selection 31/07/22
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.
Hi @Alice_Cooper ,.
This is regarding your data believe you have an error on the data becuase the FinYerStartDate has different values in this case 2022 / 2023
Taking into accoutn the data it should all have the same date correct?
Regards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsThank you Miguel,
I see it now July 2 2022 should be EOMLeaseStartDate July 31 2023 and FinYearLeaseStartDate 2023
LeaseStartDate Jun 22 2022 is correct EOMLeaseStartDate Jun 30 2022 and FinYearLeaseStartDate 2022 ...
Thank you ...
I will run some code over the data to check all records ...
Did you get a chance to help me out with the flags?
Thank you
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
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....
User | Count |
---|---|
126 | |
108 | |
99 | |
65 | |
62 |
User | Count |
---|---|
136 | |
113 | |
102 | |
71 | |
60 |