Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
julesdude
Post Partisan
Post Partisan

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

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 ReferenceUnit ReferenceUnit NumberUnit Start DateUnit End Date
AAA020AAA020-011101-Sep-18 
AAA020AAA020-021201-Aug-15 
AAA020AAA020-031308-Aug-18 
AAA020AAA020-0414+1501-Jul-20 
AAA020AAA020-051601-Jul-20 
AAA020AAA020-061701-Jan-21 
AAA020AAA020-0718.101-Dec-14 
AAA020AAA020-081901-Jul-20 
AAA020AAA020-092015-May-18 
AAA020AAA020-103501-Sep-13 
AAA020AAA020-113601-Sep-11 
AAA020AAA020-123701-Apr-12 
AAA020AAA020-1338-39-40-4101-Apr-13 
AAA020AAA020-1442-4301-Jan-1231-Dec-20
AAA020AAA020-15Antenna01-Oct-11 
AAA020AAA020-1738-39-40-41 (insegna)11-Oct-19 
AAA020AAA020-184201-Jan-21 
AAA020AAA020-194301-Jan-21 
AAA020AAA020-2018.231-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 ReferenceLease ReferenceUnit ReferenceCommencement DateExpiration DateTermination Date
AAA020ASS001AAA020-0101-Sep-1831-Aug-30 
AAA020ASS002AAA020-0201-Aug-1522-Nov-2122-Nov-21
AAA020ASS003AAA020-0308-Aug-1807-Aug-3002-Dec-21
AAA020ASS025AAA020-0401-Jul-2230-Jun-35 
AAA020ASS018AAA020-0401-Jul-2030-Jun-3330-Jun-22
AAA020ASS004AAA020-0415-Jun-1714-Jun-2930-Jun-20
AAA020ASS019AAA020-0501-Jul-2030-Jun-32 
AAA020ASS005AAA020-0515-Jun-1730-Nov-2730-Jun-20
AAA020ASS022AAA020-0601-Jan-2131-Dec-32 
AAA020ASS006AAA020-0601-Apr-1931-Dec-2031-Dec-20
AAA020ASS024AAA020-0704-Feb-2203-Feb-28 
AAA020ASS007AAA020-0701-Dec-1431-Dec-2131-Dec-21
AAA020ASS020AAA020-0801-Jul-2030-Jun-32 
AAA020ASS008AAA020-0801-Sep-1531-Aug-2730-Jun-20
AAA020ASS009AAA020-0915-May-1814-May-30 
AAA020ASS010AAA020-1001-Sep-1331-Aug-27 
AAA020ASS011AAA020-1101-Sep-1131-Aug-23 
AAA020ASS012AAA020-1201-Apr-1231-Mar-24 
AAA020ASS013AAA020-1301-Apr-1331-Mar-26 
AAA020ASS014AAA020-1401-Jan-1231-Dec-2031-Dec-20
AAA020ASS015AAA020-1501-Oct-1130-Sep-23 
AAA020ASS017AAA020-1711-Oct-1931-Mar-26 
AAA020ASS021AAA020-1801-Jan-2131-Dec-32 
AAA020ASS023AAA020-1901-Mar-2228-Feb-29 
AAA020ASS007AAA020-2001-Dec-1431-Dec-2131-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?

 

1 ACCEPTED SOLUTION
v-jialluo-msft
Community Support
Community Support

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

vjialluomsft_0-1668419745373.png

 

(3) The end result

vjialluomsft_2-1668419840614.png

 

 

 

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.

View solution in original post

4 REPLIES 4
v-jialluo-msft
Community Support
Community Support

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

vjialluomsft_0-1668419745373.png

 

(3) The end result

vjialluomsft_2-1668419840614.png

 

 

 

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
Super User
Super User

@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...

 

 

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

 

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:

 

julesdude_0-1668035879718.png

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?

 

 

 

 

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.