How to join multiple date fields from fact table to Dim Date table

[topkapi]

Hi All,

I have an issue, I need to join 2 date fields from my fact table (Start_Date), (End_Date) with date dim. The table allows me only one join. Alongwith these 2 dates other factors are also there need to be analyzed. Is there any smart solution that will work for everything. Thanks

[Anonymous]

I have a similar situation wherein I have the following (simplified) model:

 My Date table has several Fiscal calendar columns (FiscalPeriod, FiscalQuarter, etc.).

I want to be able to create one visualization depicting ApplicationCost[Cost] by Date[FiscalPeriod], and another visualization depicting Application[Disposition] by Date[FiscalQuarter].

Unfortunately, the indirect relationship between Date and ApplicationCost (via Application) is preventing me from creating a direct relationship between ApplicationCost and Date.

More abstractly, I suppose what I want is for my custom Date table to be treated the same as the built-in Date table, insofar as the various Date columns in my model are related to the Date table independently of other relationships in the model. Is this possible?

[ArulselvanD07]

Could you please share some sample data, measures you want to calcualte and the expected results of your model?

[topkapi]

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

[Ashish_Mathur]

Hi @topkapi,

This should not be difficult to solve.  Could you share the exact result you are expecting.

[topkapi]

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]

Hi,

You may download my PBI file from here.

Hope this helps.

[topkapi]

Thanks Ashish, looks helpful.

[Ashish_Mathur]

You are welcome.  If my reply helped, please mark it as Answer.

[topkapi]

But I am still unable to provide a single dim - date on x-axis to see trend to both.

[dawidvh · Accepted solution]

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

[ChrisMendoza]

@topkapi, 

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.

[topkapi]

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

[BhaveshPatel]

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

[topkapi]

Thanks Bhavesh, but i need to provide slicer(based on dimdate) to user to pick a date range. Is there any other way round?