Fact measure of two different dates column from dimension table

[Anonymous]

Hi all,

Need help I have a dimension for the incident and fact table which calculates the count of incidents open, closed or resolved in a given month.

My date dimension is linked to my fact basis a date key. I am want to create a measure in fact table that will calculate the number of incidents closed in the reporting month selected in the filter(which is based on the date table) irrespective of the month they are opened. So for eg, there might be an incident which was opened in Jan but closed in Feb. It should come under calculation when the user is selecting Feb as reporting month.

I have searched the net and found out that we can use the "USERELATIONSHIP" Dax function for such types of calculations, but I am not sure how to create this relationship into the fact table from my date dimension.

So basically I have a created_at and closed_at columns in my dimension. Right now my date dimension is linked to the fact using created_at. But I also want to use closed_at.

Please help

[amitchandak]

Refer to this blog, this has complete example: https://community.powerbi.com/t5/Community-Blog/HR-Analytics-Active-Employee-Hire-and-Termination-tr...

[Anonymous]

@amitchandak Thanks for the suggestion I have already gone through the solution on the link. But I am confused :

Let me clear my requirements so I have dimension tables like Incidents, Problem, Change, and Task. Task table is the mail table everything either Incident, Problem or Change is created as the task in the Task Table.

The Task table contains various date columns, but the ones are of my interests are opened_at and closed_at. Using the Task table and other dimensions like Incident, Problem and Change I have created a Fact table with all the surrogate keys from other dimensions.

I have created a Date dimension table in my model with a DateKey as a sequence of numbers that are referenced in the Fact table. This field is connected to Fact Table on the basis of the opened_at column.

So every fact calculated in the fact table is based on the opend_at date. Now I want to see how many Incidents are closed in the month of March 2020 irrespective of the month is has been created. 

This is where I am not able to go forward as the fact table contains one datekey which is linked to the date dimension. 

How can I use the closed_at date also in the fact table and connect my date dimension into the fact table based on the closed_at date?

I hope this clarifies.

[amitchandak · Accepted solution]

Is that not the same as an analysis done on termination in the blog.

We join both open and close date with the same date dimension. Open as active and Close is inactive and use userelation to use the join of close.

Also if you want to join the close date analysis alone and not going to interact (means other fact and not need to display along with open date), you can use the close date itself to analyze.

If this does not help then can you share sample data and sample output.

Appreciate your Kudos.

[amitchandak]

@Anonymous Does the above reply helps. if you need more help make me @

Appreciate your Kudos.