Trend Created Vs Resolved

[PontusLSpringer]

Hi, 

I've created an organizational report for customer and partner support.

I struggle a bit in one of the charts where i want to present by year and month how many support tickets that was created, and how many was resolved in each month.

When i try to build this with "CreatedOn" and "ResolvedOn", both trend lines group to the X axis "YearMonth" (based on CreatedOn date).

All red arrows are cases created in that month, but resolved in February this year.
And i know that because we did not store the resolved date prior 1st feb 2021.

i would expect that the chart would display no blue line until february, and all those would have been stacked in february.

i've tried moving Resolved to Secondary values, but i think the main issue is "YearMonth".

YearMonth is basically a custom PowerBI field with a few rules:

1. Create new conditonal column for "year", based on CreatedOn

2. Create new conditonal column for "month", based on CreatedOn

3. Create new conditonal field to generate "YearMonth", based on year and month columns.

So probably since YearMonth is based on CreatedOn dates, it's causing ResolvedOn to group as CreatedOn.

But how do i in one chart have 2 individual trendlines, not related to each other?

So once again, i want to see how many tickets that where created in January 2021 and how many tickets where resolved in January.

Thanks

Pontus Leppäniemi Springer

[Anonymous]

Hello,

      I am running into same issue and looking for something that Jira shows for created and resolved. Which Chart did you select below from available charts in PowerBI below which only shows  Axis and Values ? I dont see from the list of charts that shows similar to your screenshot posted below. Appreciate help on this if i am missing to import any charts 

[parry2k · Accepted solution]

@PontusLSpringer first you need a date dimension in your model which you can create from my blog post here.

Now set the relationship between this new data table with createdon and resolvedon date , it will be one to many, one on the date dimension side. Also, one relationship will be active and the other will be inactive, assuming you first created the relationship between the date and createdon, it will be an active relationship and other one will be an inactive relationship.

Add following measures:

Created Count = COUNTROWS ( YourTable )

Resolved Count = CALCULATE ( [Created Count], USERELATIONSHIP ( DateTable[Date], YourTable[ResolveDate] ) )

on the x-axis, use columns like a month or year or date from the date table and above two measures and you will get the correct count.

Check my latest blog post Compare Budgeted Scenarios vs. Actuals I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!

⚡Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.⚡