Filtering Date Results in Two Tables Using User Selected As Of Date

[julesdude]

Hi All,

I have a from/to date slicer with a hidden from date the user can't change, but an 'as of date' that the user can change to filter all items by date up to the chosen point in time.

I want a matrix table in my report to have in its first column a list of items relevant to the chosen selected date. To qualify this list of items, it must refer to Table1 (below example)

Table1 example:

Building Reference	Unit Reference	Unit Number	Unit Start Date	Unit End Date
AAA020	AAA020-01	11	01-Sep-18
AAA020	AAA020-02	12	01-Aug-15
AAA020	AAA020-03	13	08-Aug-18
AAA020	AAA020-04	14+15	01-Jul-20
AAA020	AAA020-05	16	01-Jul-20
AAA020	AAA020-06	17	01-Jan-21
AAA020	AAA020-07	18.1	01-Dec-14
AAA020	AAA020-08	19	01-Jul-20
AAA020	AAA020-09	20	15-May-18
AAA020	AAA020-10	35	01-Sep-13
AAA020	AAA020-11	36	01-Sep-11
AAA020	AAA020-12	37	01-Apr-12
AAA020	AAA020-13	38-39-40-41	01-Apr-13
AAA020	AAA020-14	42-43	01-Jan-12	31-Dec-20
AAA020	AAA020-15	Antenna	01-Oct-11
AAA020	AAA020-17	38-39-40-41 (insegna)	11-Oct-19
AAA020	AAA020-18	42	01-Jan-21
AAA020	AAA020-19	43	01-Jan-21
AAA020	AAA020-20	18.2	31-Jan-22

The list should return 18 results. It must check:

1. the Unit Start Date is before the As Of Date selected by the user

2. The Unit End Date is blank OR after the As Of Date selected by the user if there is a date there.

I then need to align a number of different columns from other tables against Unit items in the list being generated.

I won't go into every single one I need to add, but it would be great to also have some help along with the first. Here's an example of one column I need to create, referencing from table2:

Table2 example:

Asset Reference	Lease Reference	Unit Reference	Commencement Date	Expiration Date	Termination Date
AAA020	ASS001	AAA020-01	01-Sep-18	31-Aug-30
AAA020	ASS002	AAA020-02	01-Aug-15	22-Nov-21	22-Nov-21
AAA020	ASS003	AAA020-03	08-Aug-18	07-Aug-30	02-Dec-21
AAA020	ASS025	AAA020-04	01-Jul-22	30-Jun-35
AAA020	ASS018	AAA020-04	01-Jul-20	30-Jun-33	30-Jun-22
AAA020	ASS004	AAA020-04	15-Jun-17	14-Jun-29	30-Jun-20
AAA020	ASS019	AAA020-05	01-Jul-20	30-Jun-32
AAA020	ASS005	AAA020-05	15-Jun-17	30-Nov-27	30-Jun-20
AAA020	ASS022	AAA020-06	01-Jan-21	31-Dec-32
AAA020	ASS006	AAA020-06	01-Apr-19	31-Dec-20	31-Dec-20
AAA020	ASS024	AAA020-07	04-Feb-22	03-Feb-28
AAA020	ASS007	AAA020-07	01-Dec-14	31-Dec-21	31-Dec-21
AAA020	ASS020	AAA020-08	01-Jul-20	30-Jun-32
AAA020	ASS008	AAA020-08	01-Sep-15	31-Aug-27	30-Jun-20
AAA020	ASS009	AAA020-09	15-May-18	14-May-30
AAA020	ASS010	AAA020-10	01-Sep-13	31-Aug-27
AAA020	ASS011	AAA020-11	01-Sep-11	31-Aug-23
AAA020	ASS012	AAA020-12	01-Apr-12	31-Mar-24
AAA020	ASS013	AAA020-13	01-Apr-13	31-Mar-26
AAA020	ASS014	AAA020-14	01-Jan-12	31-Dec-20	31-Dec-20
AAA020	ASS015	AAA020-15	01-Oct-11	30-Sep-23
AAA020	ASS017	AAA020-17	11-Oct-19	31-Mar-26
AAA020	ASS021	AAA020-18	01-Jan-21	31-Dec-32
AAA020	ASS023	AAA020-19	01-Mar-22	28-Feb-29
AAA020	ASS007	AAA020-20	01-Dec-14	31-Dec-21	31-Dec-21

So, the logic here is similar to the Table1 above. It needs to:

1. Use the Unit Reference already now displayed in the row of the table in the report from the above measure. Use this Unit Reference to apply a filter filtering Unit Reference for Table2.

2. From these results, qualify the one where there is an active lease running - for this the As Of Date selected by the user must be between the Commencement Date, and the Expiration Date or the Termination Date if it is there.

This will return one row result from Table2 and I need to retrieve some of the other columns in Table2 i.e. return the corresponding Commencement Date and Expiration Date form the same row. But I guess I can apply the same filter logic to pull those out too.

Can anyone help please with the above?

[v-jialluo-msft · Accepted solution]

^{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.}

[amitchandak]

@julesdude , 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

https://community.powerbi.com/t5/Community-Blog/HR-Analytics-Active-Employee-Hire-and-Termination-tr...

[julesdude]

Hi @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?