Non-Dev Chart(Common Date Table)

[Anonymous]

Hi Guys,

Kindly help me on this long pending issue where I want to create a graph like below showing both the closed and open tickets together. I have created a common date table but somehow I'm not able to get correct data.

Expected Graph : 

Below is the link for a sample PBIX file where you can see the created and closed tickets seperately giving correct info but when combining the data is not correct. 

Kindly help in rectifying the same as its an urgent issue for me now.

Regards,

Himanshu

PBIX Location  :  https://drive.google.com/file/d/1St6rjBw3PKmcD5yl_EorxRzoY-rVz9QY/view?usp=sharing  

[amitchandak]

@Anonymous , few changes were need. Both Dates has timestamp. So created dates without time stamp.

Join both with date tables. changed one measure to use close date.

Only corrected the first visual on left.

Please find file after signature

[Anonymous · Accepted solution]

I actually got the solution using the userelationship measure and by linking the month year number with the date tables month year number. It was not working with the Date and the sample file shared by you was also not fully correct.

But finally found the solution 🙂 and the graph looks perfect now.

Measure used:

no of tickets by closed date = calculate(DISTINCTCOUNT('devops-query-spr2'[Work Item Id]), userelationship('devops-query-spr2'[Closed Month Year Number], 'DAX Date Table'[Month Year Number]),'devops-query-spr2'[Tags Type] = "NON-DEV")