Date Table

[Anonymous]

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

[Jihwan_Kim]

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

)

)

https://www.dropbox.com/s/ix3i5tnx75dihez/himanshuv2.pbix?dl=0 

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]

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.

[amitchandak]

@Anonymous , In this can you need(based on visual) one date table joining to three dates. If one table has one then one date, then use userelationship to activate inactive join

refer

https://community.powerbi.com/t5/Community-Blog/HR-Analytics-Active-Employee-Hire-and-Termination-trend/ba-p/882970

Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.

[Anonymous]

@amitchandak  @Jihwan_Kim Please use this link to download the sample pbix.

I have some data in the file where you can see the created and closed tickets. I want a single graph for only "Non-Dev" tickets where closed and created can be viewed together.

https://www.dropbox.com/s/bzur2v335qpxsjr/Sample%20-%20Copy.pbix?dl=0