Forum Discussion
How to join multiple date fields from fact table to Dim Date table
Hi topkapi
From the above, I understand that you are trying to generate a line chart to show the number of people who joined or left the organisation as displayed over a certain time frame (x axis). If this is the case, you can use USERELATIONSHIP in a measure for the date fields that have the relationships that are not active.
Let me know if you dont come right.
Display multiple date fields on line graph
Helper ICould you please share some sample data, measures you want to calcualte and the expected results of your model?
here is a subset of data (sample slective fields only)
| EmpID | jobStartDate | JObEndDate |
| 93219 | Thursday, July 17, 2008 | Monday, January 1, 1900 |
| 91418 | Friday, April 1, 2005 | Monday, January 1, 1900 |
| 61508 | Friday, April 1, 2005 | Monday, January 1, 1900 |
| 70813 | Thursday, July 20, 2006 | Wednesday, February 15, 2017 |
| 70332 | Wednesday, September 9, 2009 | Tuesday, November 8, 2016 |
| 34651 | Wednesday, September 9, 1998 | Wednesday, April 11, 2018 |
| 70705 | Thursday, July 20, 2006 | Friday, December 1, 2017 |
| 70756 | Thursday, July 20, 2006 | Monday, January 1, 1900 |
| 61384 | Friday, April 1, 2005 | Monday, January 1, 1900 |
| 61432 | Friday, April 1, 2005 | Friday, April 1, 2005 |
| 97618 | Thursday, July 17, 2008 | Monday, January 1, 1900 |
| 61380 | Friday, April 1, 2005 | Monday, January 1, 1900 |
Need to calculate, e.g, No of Emp left job, No of Current Emp, No of New Emp (during selected date range).
having date Monday, January 1, 1900 mean current emp. Hope it will make understand the scenario. Thanks
- BhaveshPatel
Super User
I don't think you would need the multiple joins with the date table.
You can use CALCULATE, DATESBETWEEEN and DATESINPERIOD to get what you are after.
The calculation logic would be straightforward.
Thanks,
Bhavesh
Thanks Bhavesh, but i need to provide slicer(based on dimdate) to user to pick a date range. Is there any other way round?
- ChrisMendoza
Resident Rockstar
Create two measures:
No of Emp left job = CALCULATE(COUNT(YourTable[EmpID]),YourTable[JObEndDate] = DATE(1900,1,1) ) No of Current Emp = CALCULATE(COUNT(YourTable[EmpID]),YourTable[JObEndDate] > DATE(1900,1,1) )
Then create a slicer on dCalendar Table with a relation to your fData Table. Turns out choosing jobStartDate vs. JObEndDate makes a difference. You'll have to decide which you like.
Thanks Dozer. But this solution doesn't cover the 2nd part which is based on Start-Date. I need to calculate New-Emp who started (mentioned in Start-Date) during the date range selected on slicer slider (from Dim_date). So here comes issue to accomodate one Date-Dim for 2 dates in data table.
Thanks
- Ashish_Mathur
Hi topkapi,
This should not be difficult to solve. Could you share the exact result you are expecting.
Thanks Ashish for the reply. I am just trying Dozer's solution. I cant present any results but can explain scenarios.
To calucate Current-Emp and Left-Emp need to use End-Date, mean if there is any end date mentioned it's mean Emp has left on that date otherwise '1900-01-01' mean is current.
The sencond part is that need to count NewEmp based on Start-Date, another field in the Emp table.
So when ever user selects a date range from slicer slider, start & end date he should get current-emp and left-emp calucated based on end-date (condition explained earlier). Secondly need to calculate no. of NewEmp (based on Start-Dates lie between date range selected on slicer slider). For slicer slider I am using Dim-Date table.
Thanks
- Ashish_Mathur
