Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago

Date Table

Hi Guys,

 

I have a tricky situation in my model where I have 2 date tables ( created date and closed date).

 

I have created a date table using dax but the issue is how to sync all 3 tables together?

 

Currently i have a relationship of my dax date table with my created date in my model and the graph shows correct data for the created date whereas the data for closed date is not correct and when i make the relationship of my dax date table with the closed date the numbers for created date are wrong. 

 

Any idea how to fix this? I want to show created and closed numbers together.

 

Please Note : I have two date tables in my model i.e Created and closed. I have created another date table using DAZ to setup the relationship.

 

 

 

Regards,

Himanshu

 

 

Need Help

4 Replies

  • Jihwan_Kim's avatar
    Jihwan_Kim
    Super User
    5 years ago

    Hi, Anonymous 

    I tried to create a sample pbix file based on the explanation.

     

    I suggest having a data model like the one below.

    One dim-date table with inactive relationships, and use the USERELATIONSHIP function if needed.

    All measures are in the sample pbix file.

     

     

    Created Measure =
    CALCULATE (
    COUNTROWS ( 'Cases' ),
    USERELATIONSHIP ( Dates[Date], 'Cases'[CreatedDate] )
    )
     
    Closed Measure =
    CALCULATE (
    COUNTROWS ( 'Cases' ),
    USERELATIONSHIP ( Dates[Date], 'Cases'[ClosedDate] )
    )
     
    Open Measure =
    CALCULATE (
    COUNTROWS ( 'Cases' ),
    FILTER (
    'Cases',
    'Cases'[CreatedDate] <= MAX ( Dates[Date] )
    && 'Cases'[ClosedDate] >= MIN ( Dates[Date] )
    )
    )
     
     
     
     

    Hi, My name is Jihwan Kim.


    If this post helps, then please consider accept it as the solution to help other members find it faster, and give a big thumbs up.


    Linkedin: linkedin.com/in/jihwankim1975/

    Twitter: twitter.com/Jihwan_JHKIM

     

    • Anonymous's avatar
      Anonymous
      Not applicable
      5 years ago

      thanks for the response.

       

      I tried this approach but iys not working. I have shared a sample pbix file, let me know if this can be done.