Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
8 years ago
Solved

count events in progress steps needed

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 experi...
Need Help
Tutorial Requests
  • TomMartens's avatar
    TomMartens
    8 years ago

    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

     

     

Anonymous's avatar
Anonymous
Not applicable
8 years ago

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

Anonymous's avatar
Anonymous
Not applicable
8 years ago

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]))   
   )