Showing results for 
Search instead for 
Did you mean: 
Helper II
Helper II

Dates, Dates and Complexities A BIG ASK

Hi guys,

I am looking for some help – This is a BIG ASK …

I would like to do the following:

  1. 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.
  2. 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).
  3. 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 
  4. 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).
  5. 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.
  6. I can then use (Selection-Flag) to create viz’s for the filter selected for example a Map of (Leases-In) by suburb etc…
  7. 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..TblHeadleaseTblHeadlease

Community Support
Community Support

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.


  1. Since there is no dynamic filtering I think I need to re-think this ..


  1. The filter is about time. I am thinking that the user is presented with End Of Month Report (EOMRPT) filter on the Page
  2. Let’s say they select Oct 19 (EOMRPT)… using your methods to structure a fiscal year and Calendar year I need to work out:
    1. That selected EOMRPT (Oct 19) falls within Fin Year 19-20
    2. The number of properties Fin Year To Date (1 Jul 19 to selected EOMRPT), the number of Leases Started (Lease-In) during that period (1 Jul19 to selected EOMRPT) and the number of Leases Ended (Lease-Out).
    3. The number of properties, (Lease-in), (Lease-out) for previous Fin Year (18-19) … IF No Values can we return “No Data”?
    4. The same pattern for calendar year, number of properties, (lease-in) and (lease-out) Year To Date (1 Jan 2019 to selected EOMRPT) and then
    5. Number of properties, (lease-in) and (lease-out) for previous calendar year
  3. I would also like to return, based on selected EOMRPT filter
    1. Number of properties, (lease-in0, (lease-out) during the selected EOMRPT… in the example selecting Oct 19, I would like to know how many leases were started (lease-in) during 1/10/19 to 30/10/19 and how many leases ended (lease out) during 1/10/19 to 31/10/19
Property IDEOMRPTLease Start DateLease Term MonthsLease End DateSuburbDwelling TypeNumber of BedroomsFin YearCal YearFin Year To DateCal Year To Date
1May-191/04/201961/10/2019Spring HillUnit218-192019  
5Jun-192/06/201962/12/2019Spring HillUnit218-192019  
6Jul-1930/06/20191230/06/2020Red HillDetached319-202019  
7Jul-191/07/2019121/07/2020Petrie TerraceTownshouse219-202019  
8Jul-192/07/2019182/01/2021Mount GloriousDetached419-202019  
10Aug-199/08/201969/02/2020Mount GloriousDetached419-202019  
11Sep-193/09/201963/03/2020Petrie TerraceTownshouse219-202019  
12Sep-194/09/2019124/09/2020Mount GloriousDetached419-202019  
13Sep-195/09/201965/03/2020Petrie TerraceTownshouse219-202019  
16Oct-1928/09/20191228/09/2020Spring HillUnit219-202019  
17Nov-1915/11/2019615/05/2020Arana HillsUnit219-202019  
19Nov-1917/11/2019617/05/2020Spring HillUnit219-202019  
20Nov-1918/11/2019618/05/2020Arana HillsUnit219-202019  
21Dec-1929/11/2019629/05/2020Ferny GroveDetached319-202019  
22Dec-1930/11/2019630/05/2020Mount GloriousDetached419-202019  
24Jan-207/01/202067/07/2020Mount GloriousDetached419-202020  
25Jan-208/01/202068/07/2020Spring HillUnit219-202020  
26Feb-202/02/2020122/02/2021Mount GloriousDetached319-202020  
27Feb-203/02/202063/08/2020Mount GloriousDetached419-202020  
29Apr-201/04/202061/10/2020Red HillUnit219-202020  
30Apr-202/04/202062/10/2020Mount GloriousDetached419-202020  
31Apr-203/04/202063/10/2020Surfer ParadiseUnit119-202020  
33Jun-2015/06/2020615/12/2020Petrie TerraceTownshouse219-202020  
34Jul-201/07/202061/01/2021Mount GloriousDetached420-212020  
36Sep-203/09/202063/03/2021Red HillTownshouse220-212020  
37Sep-204/09/202064/03/2021Surfer ParadiseUnit120-212020  
38Sep-205/09/202065/03/2021Paradise IslandUnit120-212020  
45Feb-215/02/2021125/02/2022Spring HillUnit220-212021  
46Feb-216/02/202166/08/2021Surfer ParadiseUnit120-212021  
47Feb-217/02/2021127/02/2022Paradise IslandUnit120-212021  
49Apr-214/04/202164/10/2021Surfer ParadiseUnit120-212021  
50Apr-215/04/202165/10/2021Petrie TerraceTownshouse220-212021  
51Apr-216/04/202166/10/2021Surfer ParadiseUnit120-212021  
52May-215/05/202165/11/2021Paradise IslandUnit120-212021  
53Jun-2130/06/2021630/12/2021Samford ValleyDetached320-212021  
54Jun-211/07/2021121/07/2022Surfer ParadiseUnit120-212021  
55Jun-212/07/202162/01/2022Petrie TerraceTownshouse220-212021  
56Jul-214/07/202164/01/2022Mount NeboDetached321-222021  

Helpful resources

Winner of T-Shirt Design

Power BI T-Shirt Design Challenge 2023

Find out who won the T-Shirt Design Challenge and the top 3 finalists.

March 2023 Update3

Power BI March 2023 Update

Find out more about the March 2023 update.