Forum Discussion
Helper VHow to create a calendar table for two date columns?
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?
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.
7 Replies
- Ashish_Mathur
Super User
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.
bourne2000Thanks 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,