count events in progress steps needed

[rixmcx59]

Hello all, I have a fact table and a date table, what I am trying to do is count open tickets from previous months, count new tickets for the month and count tickets that did not close. I have experimented with some of the solutions but not getting the right result. Some detail would help, like do I add a measure or a column, and to which table? I tried the formla below as a measure and a column but the numbers are too high when used in a matrix table. The date table is not related as recommended in other posts

OpenTickets =
 CALCULATE(
  DISTINCTCOUNT('Inovation Metrics'[RemID]),
   FILTER( 'Inovation Metrics', 'Inovation Metrics'[DateKeyStart] <= MAX(Date_New[Date Key])),
   FILTER( 'Inovation Metrics', 'Inovation Metrics'[DateKeyClosed] = BLANK() || 'Inovation Metrics'[DateKeyClosed] > MAX( Date_New[Date Key]))   
   )

[TomMartens · Accepted solution]

Hey,

I guess that one of the articles you already have been reading is this one:

http://sqljason.com/2012/11/classifying-and-solving-events-in.html

Without sample data it's very difficult to say what's not working with your formula.

I created a little PBI sample file and I adjusted your Formula to my data model and it seems to work, at least I get what I expected 🙂

I assume you created a measure in your fact table and used the relevant date columns from your unrelated date table.

If my sample and the article from Jason Thomas does not help, I guess you have to create sample data, upload a file to onedrive or dropbox and share the link.

Regards

Tom

[rixmcx59]

Tom, the link you provided led me to the answer.

[Anonymous]

HI @rixmcx59

Could you please post some sample data and expected result as well?

Thanks
Raj

[rixmcx59]

Yes I looked at the article and many more. I am fairly new at this and can't really grasp a starting point.

What is the best way to share my.pbix? SharePoint Online One Drive?

It has 8084 rows

[TomMartens]

Upload your file to onedrive and share the link.

[rixmcx59]

RemedyData

I think I need you email to share from one drive

[TomMartens]

Hey,

maybe you can't use onedrive because sharing content with external users (outside your organization) is disabled by your O365 admin, please check if you have this option available

Maybe you have to consider to use your personal onedrive or dropbox.

Regards

Tom 

[rixmcx59]

Tom, personal one drive requires an upgrade and fee and I can't get to DropBox from my state work station, It's blocked. Any other way I can share the .pbix. I can get it to dropbox or Google drive tonght at home.

[rixmcx59]

I don't have those options. I will check the other methods.

[rixmcx59]

I am making some progress, the two measures below return the same result, I am trying to count open tickets from previous months. I need to validate the results but I think I am heading in the right direction. I am using YearMonth like 2018/Jan from the fact table and Year Month Short from the date table, Is this acceptable?

Edit to my original post: in the formula below is 2017/Dec recognized as "Less Than" 2018/Jan ? or do I need to use a different date format using month number?

CarriedOver = CALCULATE(
  DISTINCTCOUNT('ODI Inovation Metrics'[Remedy ID]),
   FILTER( 'ODI Inovation Metrics', 'ODI Inovation Metrics'[StartYearMonth] < CALCULATE(MAX(Date_New[Year Month Short]))),
   FILTER( 'ODI Inovation Metrics', 'ODI Inovation Metrics'[EndYearMonth]  > CALCULATE(MAX( Date_New[Year Month Short]))))

CarriedOverV2 = CALCULATE(
  DISTINCTCOUNT('ODI Inovation Metrics'[Remedy ID]),
   FILTER( 'ODI Inovation Metrics', 'ODI Inovation Metrics'[StartYearMonth] < MAX(Date_New[Year Month Short])),
   FILTER( 'ODI Inovation Metrics', 'ODI Inovation Metrics'[EndYearMonth] = BLANK() || 'ODI Inovation Metrics'[EndYearMonth] > MAX( Date_New[Year Month Short]))   
   )

[rixmcx59]

Thanks for the reply, the table below is counting new tickets submitted, I need to add two measures, I need to count tickets carried over from previous months and tickets that did not close during the month. I have a Submitted date and a closed date in the table.