Hi guys,
I am looking for some help – This is a BIG ASK …
I would like to do the following:
- Set-up a filter that allows the user to select how they want to see the results of Property Count; how many Leases have started and how many Leases have ended for the filter selection problem (Lease-In), (Lease-Out). BUT here are the problem as multiple properties can be entered during the End Of Month Report (EOMRPT). I am thinking some aggregate function may be required.
- I would like to build a two-level filter selection. The first level has the values of Financial Year (Fin Year), Calendar Year (Cal Year) or End Of Month Report (EOMRPT).
- Based on the selection, the user needs to be presented with the values in the appropriate Column… for example if they Select Financial Year, they should then be presented with a dropdown filter selection of 18-19, 19-20,20-21, 21-22
- Once they select that then I would like to calculate how many Leases Started during the selection period (Leases-In) and how many leases ended during the selection period (Leases-Out).
- I would also like to create a new column (Selection-Flag) that has a value of 1 for the records that fall within the filter selection. The values in this column would need to be reset every time filtering is updated.
- I can then use (Selection-Flag) to create viz’s for the filter selected for example a Map of (Leases-In) by suburb etc…
- This one is potentially tricker … If the user selects End Of Month Report (EOMRPT) I would also like to use the fields (Fin Year To Date) and (Cal Year To Date) as flags, placing the value 1 if the against the records that fall within the Year To Date … for example Users Selects Aug 20 as EOMRPT. Since Aug 20 is in 20-21 Fin year, the value of 1 is set against each record in the column (Fin Year To Date) for Jul 20-21 and Aug-20-21 for the Cal Year to Date 1 would be recorded against Jan-20 through to Aug 20 … New calculated columns (FIN YTD Leases-In), (FIN YTD Leases-out), (CAL YTD Leases-In) and (CAL YTD Leases-Out) would need to be calculated.
To make things easier the Dataset Name is TblHeadlease and the column names are in brackets above..
Hi, @Alice_Cooper
1. In response to the first question, I don't know much, is it a filter about time, or a filter for residential type, please explain with examples.
2. You can try the following methods to structure a fiscal year.
Date table Column:
Fin Year =
RIGHT ( IF ( MONTH ( [Date] ) <= 6, YEAR ( [Date] ) - 1, YEAR ( [Date] ) ), 2 ) & "-"
& RIGHT (
IF ( MONTH ( [Date] ) <= 6, YEAR ( [Date] ) - 1, YEAR ( [Date] ) ) + 1, 2 )Cal Year = YEAR([Date])table column:
Fin Year = LOOKUPVALUE('Date'[Fin Year],'Date'[Date],[EOMRPT])Cal Year = LOOKUPVALUE('Date'[Cal Year],'Date'[Date],[EOMRPT])3. The third question is, is this the result you expect?
4. Please elaborate with examples.
5.You can't create dynamic columns, but you can create dynamic Measure.
Measure = IF(SELECTEDVALUE('Date'[Fin Year])=SELECTEDVALUE('Table'[Fin Year]),1,0)
I only simulated 20 pieces of data from you, if you need more, try to provide Excel data. Please tell us what you expect the output to be.
Best Regards,
Community Support Team _Charlotte
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Helpful resources
Power BI T-Shirt Design Challenge 2023
Find out who won the T-Shirt Design Challenge and the top 3 finalists.
