How to create a calendar table for two date columns?

[bourne2000]

Hi

I am building an HR Analytics dashboard. My data has the Employee joining date and the Employee leaving date. I would like to do an analysis using employee joining and leaving dates. 

I wanted to create a separate Calendar column, however, not sure how to do it for both date columns. Can anyone advise how to do this?

[bourne2000]

@Ashish_Mathur 

Thanks for your quick reply. I am getting an error. 

{Number.From([Joining Date])..Number.From([Leaving date])}  --> What this forumla doing? Is it subtracting the joining and leaving date? 

I also wanted to do an analysis separately on leaving and joining dates, I might be using a date slicer for both joining and leaving dates on separate page. Can you please advise whether the above formula works for this?. Sorry I am a novice to the Power BI

[Ashish_Mathur]

Hi,

Share the link from where i can download your PBI file.  If you imported data in the PBI file from an MS Excel file, then i will need that MS Excel file as well.  If data is confidential, then please anonymise the data before sharing the download link.

[bourne2000]

@Ashish_Mathur  Thanks. Please find the pbix and excel file. I gave some test data

You can download the file from the below link,

https://we.tl/t-5ZgcI0FvZ2

[Ashish_Mathur · Accepted solution]

Your test data was incorrect.  Your leaving date was before the joining data - i made that correction.  Download the PBI file from here.

The next time you share data, please atleast ensure that you share logical data.

[bourne2000]

@Ashish_Mathur  Thanks for your support yesterday. I tried to replicate the same in my data. I am getting the below error,

{Number.From([Hire Date])..Number.From([Terminated date])}

Can you please advise?

[Ashish_Mathur]

Hi,

Maybe your systems understand the mm/dd/yyyy format (not the dd/mm/yyyy format).  In the source Excel document, ensure that all dates are entered (not just formatted) in the mm/dd/yyyy format.  

[Ashish_Mathur]

Hi,

In the Query Editor, you should write this custom column formula

={Number.From([Joining Date])..Number.From([Leaving date])}

This will create an additional column with one date per cell between the Joining date and Leaving date.  Your Calendar Table should then be related to this Date column.