Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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,
Solved! Go to 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
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
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.
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
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] ) )
)
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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
112 | |
97 | |
85 | |
67 | |
59 |
User | Count |
---|---|
150 | |
120 | |
99 | |
87 | |
68 |