Forum Discussion

topkapi's avatar
topkapi
Frequent Visitor
8 years ago
Solved

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 ...
  • dawidvh's avatar
    dawidvh
    8 years ago

    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

ArulselvanD07's avatar
ArulselvanD07
Helper I
8 years ago

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

  • topkapi's avatar
    topkapi
    Frequent Visitor
    8 years ago

    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

    • BhaveshPatel's avatar
      BhaveshPatel
      Super User
      8 years ago

      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's avatar
        topkapi
        Frequent Visitor
        8 years ago

        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's avatar
      ChrisMendoza
      Resident Rockstar
      8 years ago

      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's avatar
        topkapi
        Frequent Visitor
        8 years ago

        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's avatar
      Ashish_Mathur
      Super User
      8 years ago

      Hi topkapi,

       

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

      • topkapi's avatar
        topkapi
        Frequent Visitor
        8 years ago

        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