Events in progress not returning expected result

[rixmcx59]

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,

[Anonymous]

Hi @rixmcx59

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

[rixmcx59]

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 · Accepted solution]

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

[rixmcx59]

Tom, your original post (in another thread) guided me to the answer, thanks

S3 = Calculate (
  CountRows ( 'ODI Inovation Metrics' ),
  Filter (Values ( 'ODI Inovation Metrics'[Submit Date] ), 'ODI Inovation Metrics'[Submit Date] <= Max ( Date_New[Date] )  ),
  Filter (Values ( 'ODI Inovation Metrics'[Closed Date] ), 'ODI Inovation Metrics'[Closed Date] = BLANK()|| 'ODI Inovation Metrics'[Closed Date] >=Min ( Date_New[Date] )  )
                 )

[rixmcx59]

Thanks for the response, I created a new  column and used the the date/short date format and got the same result, the fields below are the actual dates.

CarriedOver2 = CALCULATE(
  DISTINCTCOUNT('ODI Inovation Metrics'[Remedy ID]),
   FILTER( 'ODI Inovation Metrics', 'ODI Inovation Metrics'[Submit Date] <= MAX(Date_New[Date])),
   FILTER( 'ODI Inovation Metrics', 'ODI Inovation Metrics'[Closed Date] = BLANK() || 'ODI Inovation Metrics'[Closed Date] >= MAX( Date_New[Date]))   
   )

Should I be using a new column or a measure? table or a matrix? when I run this in MS Access I get the right result,

SELECT [ODI Inovation Metrics_1].[Remedy ID], [ODI Inovation Metrics_1].[Submit Date], [ODI Inovation Metrics_1].[Closed Date]
FROM [ODI Inovation Metrics] AS [ODI Inovation Metrics_1]
WHERE ((([ODI Inovation Metrics_1].[Closed Date])>=#2/1/2014# Or ([ODI Inovation Metrics_1].[Closed Date]) Is Null) AND ((Month([Submit Date]))<=2) AND ((Year([Submit Date]))<=2014));

thanks