cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
eamonnde Frequent Visitor
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
ManNVSM Member
Member

Re: Combining two Date Columns on the one report page

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.

eamonnde Frequent Visitor
Frequent Visitor

Re: Combining two Date Columns on the one report page

Hi @ManNVSM,

 

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

 

ManNVSM Member
Member

Re: Combining two Date Columns on the one report page

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.

eamonnde Frequent Visitor
Frequent Visitor

Re: Combining two Date Columns on the one report page

Hi @ManNVSM,

 

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

ManNVSM Member
Member

Re: Combining two Date Columns on the one report page

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.

eamonnde Frequent Visitor
Frequent Visitor

Re: Combining two Date Columns on the one report page

Hi @ManNVSM

 

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
Exclusive LIVE Community Event #3 – Ask Arun Anything

Exclusive LIVE Community Event #3 – Ask Arun Anything

Join us in the third Triple A event!

Meet the 2020 Season 1 Power BI Super Users!

Meet the 2020 Season 1 Power BI Super Users!

It’s the start of a new Super User season! Learn all about the new Super Users and brand-new tiered recognition system.

Super User Challenge: Can You Solve These?

Super User Challenge: Can You Solve These?

We're celebrating the start of the New Super User season with our first ever Super User 'Can You Solve These?' challenge.

January 2020 Community Highlights

January 2020 Community Highlights

Make sure you didn't miss any of the things that happened in the community in January!

Top Solution Authors