Forum Discussion
Post PartisanFiltering Date Results in Two Tables Using User Selected As Of Date
Hi julesdude ,
Please follow these steps:
(1) Create a new measure
FLAG = IF ( MAX ( 'Table1'[Unit Start Date] ) <= MAX ( 'Date'[Date] ) && ( MAX ( 'Table1'[Unit End Date] ) >= MAX ( 'Date'[Date] ) || MAX ( 'Table1'[Unit End Date] ) = BLANK () ), 1 )(2) Set up filtering
(3) The end result
Best Regards,
Gallen Luo
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Super Userjulesdude , That date in slicer should not have any join with you table and then you can try a measure like
//Date1 is independent Date table
new measure =
var _max = maxx(allselected(Date1),Date1[Date])
return
calculate( sum(Table[Value]), filter('Table', 'Table'[Unit Start Date] <=_max && (isblank('Table'[Unit End Date]) && 'Table'[Unit End Date] >=_max)))
Also, HR Blog and files can help
Post PartisanHi amitchandak
Unfortunately that didn't work in obtaining the Unit list as above. I can see the logic is the right direction, but I think there might need to be an OR operator in there. I get zero results returned. To recap:
1. the Unit Start Date is before the As Of Date selected by the user
>this is working ok
2. The Unit End Date is blank OR if there is a date there, the As Of Date selected by the user is before this date
Then for the next column against this list, needs to ref table2:
1. Use the Unit Reference already now displayed in the row of the table in the report - created by the above measure. Use this Unit Reference to filter Unit Reference column for Table2.
2. From the filtered results, qualify the one where there is an active lease running - the As Of Date selected by the user must after the Commencement Date, and before the Expiration Date or Termination Date if a Termination Date is there
- julesdude
Just tried some variations on the above.
I tried the AND && and OR || operators but I'm only getting one result for the first two steps from my post I'm applying.
unit list = var _max = [As Of Date] // takes the user selected calendar date return calculate( MAX(Table1[Unit Number]), filter(Table1, Table1[Unit Start Date] <=_max && (isblank(Table1[Unit End Date]) || Table1[Unit End Date] >=_max)))I get this:
But I'm expecting to see the following:
Unit Number 11 12 13 14+15 16 17 18.1 19 20 35 36 37 38-39-40-41 Antenna 38-39-40-41 (insegna) 42 43 18.2 ....which shows a list of 18 out of the 19 results from table1, because, if I use today's date, one row item has a Unit End Date in the past.
Where am I going wrong?