Hours in days from Date 1 (in current table) until Date 2 (in another table)

[jfernand]

Hi team,

I'm trying to make a new column or filter that displays the hours in my "Schedule" column from 01-01-2017 until the "Date_to" from another table "Resources". See the below example.

Currently the "Schedule" table displays ALL hours from 01-01-2017 until 31-12-2099. I want to filter this table so that I only get the hours from 01-01-2017 until the "Date_to" from another table "Resources".

How should I proceed?

[ImkeF]

You can add a column in the query editor in your "Schedule"-table like this:

if [Date] = #date(2099,12,31) then Table.SelectRows(Resources, (Res)=>Res[ResID]=[ResID]) [Date_to] {0} else [Date]

It selects the row from the Resources where the ResID match.

[Date_to] then selects the column and returns it as a list and

{0} takes the first element from that returned list and transforms it to a single value

[jfernand]

Hi,

this looks pretty accurate to what I need. Can I make the #date(2099,12,31) a dynamic figure? 

If in my "Schedule" table I have a schedule for resource 123 from 01/01/2017 until 31/12/2019 (instead od 31/12/2099) then I would want only the schedules from 01/01/2017 until the 28-06-2017 (using the first example)

[ImkeF]

Sorry I cannot follow you here.

But you can replace the hardcoded value by a reference to another field - Just that I currently don't understand/see where this value would sit.

[jfernand]

Sorry maybe I haven't explained well enough. 

I only found time to try your solution now. By adding a column as suggested I arrived at the below result. This is the actual solution I'm trying to build: 

a resource works in a company from 01-01-2017 and leaves in 31-03-2017. this information is stored on the "Resource" table. His schedule has been generated until the end of the year 31-12-2099 but the "Schedule" table has no way of knowing if the resource has left. This means that, even though the resource has left, the company is still reporting the hours the resource should have worked. For a professional services company this cannot be since we are reporting thousands of hours of people that have already left.

The solution I want to build is to know the total hours the resource has in his schedule from the beggining of the year until the date of leave. 

[v-caliao-msft]

@jfernand,

Please create new table using the DAX below.
Table = ADDCOLUMNS(FILTER(Schedule,Schedule[Date]<=LOOKUPVALUE(Resources[DateTo],Resources[ResID],Schedule[ResID])),"NewDate",Schedule[Date])

Regards,

Charlie Liao

[ImkeF]

OK, now I understand.

1)  Join your Resources to Schedule on ResID and expand [Date_to]

2) Add a column "Filter" in Schedule: if [Date_to]<=[Date] then "in" else "out"

3) Filter Schedule on new column "Filter" for "in"

This should leave only those day in where the Resource was still employed. 

[jfernand]

I'm verry sorry for my delay but I was held back by a project. How do I "join" two tables? Using the "Merge queries" tool?

[ImkeF]

Yes. A dialogue will pop up where you can choose the columns to connect.

[jfernand]

I have tried joining them via the Merge queries tool but I get the below error.

"Expression.Error: A cyclic reference was encountered during evaluation."

What is this usually about?