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 there need to be analyzed. Is there any smart solution that will work for everything. Thanks

  • 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

14 Replies

  • 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

  • Anonymous's avatar
    Anonymous
    Not applicable
    8 years ago

    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?