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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
eamonnde
Frequent Visitor

Combining two Date Columns on the one report page

Hi,

 

I'm still getting used to Power Bi but am having an issue with trying to create a filter within my report.

 

I've two columns in "Table A" one to do with start date and another to do with end date (some instances it's null), I've created an if statement using both columns that works based off todays date using Now(), but I'm trying to get this to work as a certain point in time feature i.e. what would the view be like this day last year?

 

I've added in a Date Dimension table and have linked this table with a both a viewable and hidden relationship.

 

*** Adding more details to this I'm trying to create a view that will enable me to get the following results***

 

 Database   Results 
NameEmployee StatusCommence dateEnd date DateActive Employees
JohnPermenant01/01/2017Null 07/12/20171
PaulPermenant16/06/201607/09/2017 07/06/20172
MaryTemp12/12/2017Null   

 

Any help on this would be appreciated. Thanks

6 REPLIES 6
Anonymous
Not applicable

Dear @eamonnde,

 

Can you show more about your issue? An image/result of your expect/your formular (this is important) or share the .pbix(delete the sensitive data or create dummy data).

 

Regards,

ManNVSM.

Hi @Anonymous,

 

I've attached an image of the two tables below.

 

The issue I have is that I'm trying to use the Commenced_date and Ended_date in Table A together which will in turn impact on the results shown in the various views I have on the report page. I'm trying to get this function to work as a slicer if possible.

 

The relationship below are;

Visible - 'date'[Date] to 'Table A'[Commenced_date]

Hidden- 'date'[Date] to 'Table A'[ended_date]

Dummy.JPG

 

Anonymous
Not applicable

Dear @eamonnde,

 

Between 2 table, just only 1 column accept as Relationship_Key. Sometime you must combine 2 column to make a new Relationship_Key. In your case, just only 1: Date[Date] connect Table A'[Commenced_date]

 

You should create a new Date table link with another hidden. Using simple code when create new table: EndDate =CALENDAR (DATE (2017, 1, 1), DATE (2018, 12, 31)).

 

Regards,

ManNVSM.

Hi @Anonymous,

 

OK, I might be missing something having created the new date table with the following relationships

'date'[Date] to 'Table A'[Commenced_date]

'enddate'[Date] to 'Table A'[ended_date]

 

How does the creation of this new table enable me to create a slicer that can be used to display specific date related information in my view?

 

Thanks for the help

eamonnde

Anonymous
Not applicable

Dear @eamonnde,

 

So, can you tell me your expect in this report? It's seem your report about employee, people use to:

 

  • Calculate about 2 date => Working day (Up to date or leave)
  • Count employee join/leave in by month
  • Or something else that you want to measure...

Regards,

ManNVSM.

Hi @Anonymous

 

What I'm trying to have in place is something like what I've included in the above image with my initial query, where by I'm able to select a Single date and on that date I'm able to identify the number of people who were actively working for X company based off the start date and end date columns.

 

In excel I could do this with an IF function and i could relate it back to a single cell, using that cell run a nested IF based on a combination of greater thans, less thans and nulls.

 

Hopefully a bit clearer, thanks again

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.