Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
topkapi
Frequent Visitor

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

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

1 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 graphDisplay multiple date fields on line graph

View solution in original post

14 REPLIES 14
Anonymous
Not applicable

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

 

Simplified Model.PNG

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 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
Helper I
Helper I

Could 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)

 

EmpIDjobStartDateJObEndDate
93219Thursday, July 17, 2008Monday, January 1, 1900
91418Friday, April 1, 2005Monday, January 1, 1900
61508Friday, April 1, 2005Monday, January 1, 1900
70813Thursday, July 20, 2006Wednesday, February 15, 2017
70332Wednesday, September 9, 2009Tuesday, November 8, 2016
34651Wednesday, September 9, 1998Wednesday, April 11, 2018
70705Thursday, July 20, 2006Friday, December 1, 2017
70756Thursday, July 20, 2006Monday, January 1, 1900
61384Friday, April 1, 2005Monday, January 1, 1900
61432Friday, April 1, 2005Friday, April 1, 2005
97618Thursday, July 17, 2008Monday, January 1, 1900
61380Friday, April 1, 2005Monday, 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

Hi @topkapi,

 

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


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

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

Hi,

 

You may download my PBI file from here.

 

Hope this helps.

 

Untitled.png


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

Thanks Ashish, looks helpful.

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


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

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

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 graphDisplay multiple date fields on line graph

@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.

 

7.PNG9.PNG8.PNG






Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

Proud to be a Super User!



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

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 & Regards,
Bhavesh

Love the Self Service BI.
Please use the 'Mark as answer' link to mark a post that answers your question. If you find a reply helpful, please remember to give Kudos.

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

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

July Newsletter

Fabric Community Update - July 2024

Find out what's new and trending in the Fabric Community.