cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
jfernand Regular Visitor
Regular Visitor

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

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?

image.png

9 REPLIES 9
Super User
Super User

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

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

Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!

Imke Feldmann

How to integrate M-code into your solution -- Check out more PBI- learning resources here -- Performance Tipps for M-queries




jfernand Regular Visitor
Regular Visitor

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

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)

Super User
Super User

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

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.

Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!

Imke Feldmann

How to integrate M-code into your solution -- Check out more PBI- learning resources here -- Performance Tipps for M-queries




jfernand Regular Visitor
Regular Visitor

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

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. 
image.png

Super User
Super User

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

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. 

 

Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!

Imke Feldmann

How to integrate M-code into your solution -- Check out more PBI- learning resources here -- Performance Tipps for M-queries




Moderator v-caliao-msft
Moderator

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

@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])
Capture.PNGCapture1.PNG

 

Regards,

Charlie Liao

jfernand Regular Visitor
Regular Visitor

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

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?

Super User
Super User

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

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

Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!

Imke Feldmann

How to integrate M-code into your solution -- Check out more PBI- learning resources here -- Performance Tipps for M-queries




jfernand Regular Visitor
Regular Visitor

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

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?