Forum Discussion

PontusLSpringer's avatar
PontusLSpringer
New Member
5 years ago
Solved

Trend Created Vs Resolved

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 cre...
Need Help
Tips and Tricks
  • parry2k's avatar
    parry2k
    5 years ago

    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.⚡

parry2k's avatar
parry2k
Super User
5 years ago

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.⚡