Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
8 years ago
Solved

Events in progress not returning expected result

Hello all, the formula below is not including records started before Feb 1, 2014? why? (the date table is unrelated)

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

I verified my result and the formula is ignoring 24 records that started in Jan of 2014 and ended during or after Feb of 2014, sorry to repost this but I was not sure if you could re-start a previous post.

 

Thanks for any help,

 

Need Help
  • TomMartens's avatar
    TomMartens
    8 years ago

    Hey,

     

    I guess that the datatype of your columns that you are using is of type "text".

     

    This measure returns "unexpected":

    Measure = 
IF("2014/Jan" < "2014/Feb","Jan is before Feb", "unexpected")

    You have to change the datatype of the column that is used to something that does not use "alphanumerical" sorting, like decimal/wholenumber/date/datetime.

     

    Hopefully this is what you are looking for.

     

    Regards,

    Tom

     

5 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    8 years ago

    Hi Anonymous

     

    Need to understand your data to answer your question.

     

    Whats the value of the below 2 in your data model?

    MAX(Date_New[Year Month Short])

     

    MAX( Date_New[Year Month Short])

     

    And, whats the value of  'ODI Inovation Metrics'[StartYearMonth] &'ODI Inovation Metrics'[EndYearMonth] for the mising records?

     

    Also, i hope the relationship is detup properly.

     

    Thanks

    Raj

    • Anonymous's avatar
      Anonymous
      Not applicable
      8 years ago

      StartYearMonth and EndYearMonth are dates from the fact table, YearMonthShort is from the Date table. Other post on this site recommended no relationship between these 2 tables, then counting the ID. I am trying to count all open tickets in each month.

      • TomMartens's avatar
        TomMartens
        Super User
        8 years ago

        Hey,

         

        I guess that the datatype of your columns that you are using is of type "text".

         

        This measure returns "unexpected":

        Measure = 
IF("2014/Jan" < "2014/Feb","Jan is before Feb", "unexpected")

        You have to change the datatype of the column that is used to something that does not use "alphanumerical" sorting, like decimal/wholenumber/date/datetime.

         

        Hopefully this is what you are looking for.

         

        Regards,

        Tom