Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Measure using date range skips over dates

I have a measure that was created to count the number of bugs in a range of dates on another table based on when the bug was opened and closed. The measure is:

Sev3 = SUMX( 'Combined', COUNTX( FILTERS( 'DatesLink'[Date] ), IF( ( [Date] >= Combined[CreatedDate] && [Date] <= TODAY() ) && ( Combined[CompletedDate]=BLANK() || [Date] < Combined[ClosedDate] ) && Combined[Severity] = "3 - Medium", 1, BLANK() ) ) )

 

When I put the dates from DatesLink[Date] in a table, it is sequential (left picture), however, when I attempt to apply that measure to the same data, it skips dates (right picture). Can anyone point me to why this might be happening?

 

 

Need Help
  • parry2k's avatar
    parry2k
    6 years ago

    Anonymous it means your measure is returning blank value. You can drop down date column and click show items with no data and it will show all the dates even if measure is returning blank value.

2 Replies

  • parry2k's avatar
    parry2k
    Super User
    6 years ago

    Anonymous it means your measure is returning blank value. You can drop down date column and click show items with no data and it will show all the dates even if measure is returning blank value.

  • TomMartens's avatar
    TomMartens
    Super User
    6 years ago

    Hey Anonymous ,

     

    I'm wondering if the tables 'Combined' and 'DatesLink' are related, and if this is the case what is this relationship? My assumption: the relationship is between the columns 'DatesLink'[Date] (on the one side) and 'Combined'[CreatedDate].

     

    Maybe you might consider to check the option "Show items with no data" for the column 'DatesLink'[Date] that you are using in the table visual, the next screenshot shows how to check/uncheck this option:

    I'm also wondering, why you are using the function FILTERS, from my understanding of the formula you provided I would use VALUES instead, even if both would return the same "table", as 'DatesLink'[Date] is used on the rows.
    I would rewrite the measure like so (no idea if this solves the issue with skipped dates), but at least it chaches TODAY into a variable.

     

    Sev3 =
	var _today = [today]
	return
	SUMX(
	'Combined'  
	, COUNTX( 
		VALUES( 'DatesLink'[Date] )
		, IF(
			AND(
				AND(
					AND( [Date] >= Combined[CreatedDate] ,  [Date] <= _today ) 
					, OR ( Combined[CompletedDate]=BLANK() ,  [Date] < Combined[ClosedDate] )
				)
				, Combined[Severity] = "3 - Medium"
			)
			, 1, BLANK() 
			) 
		) 
	)

     


    Maybe the reason why dates are "skipped" if the measure is applied is simply this: it returns BLANK() for the dates from 2016 and 2017, as the bugs created in 2016 are no longer open (hopefully) 🙂

    Regards,

    Tom