Forum Discussion

cusdaiv's avatar
cusdaiv
New Member
10 years ago

Filter doesn't show complete data set

When I attempt to filter data (revenues by week), the filter stops at 2015-49 (data begins in 2014 and is ongoing). Is there a cap on the number of data points that I can sort individually? The point...
Greg_Deckler's avatar
Greg_Deckler
Community Champion
10 years ago

cusdaiv - It looks like the way your filter column is configured, it ends up as text. You might consider creating a custom column that is essentially:

 

= YEAR([date]) & MONTH([date])

 

Then, you would have a format such as:

 

201549

 

Then, you could set your filter to be "greater than" 201549 for example.

 

Tough to be sure I am answering your question correctly without sample data and such.

greggyb's avatar
greggyb
Resident Rockstar
10 years ago

Greg_Deckler, the concatenation operator will return a Text string, so inequality operators would sort lexicographically, and visual-, page-, and report-level filters would not have greater than / less than options.

 

You could use the concatenation and then change the data type to Whole Number to get the filter options. Either way, simple concatenation is inappropriate for creating a year-month field, as there are 12 months. 20159 < 201412

 

The best way to do this would be arithmetic or utilizing FORMAT():

 

// DAX
// YearMonth arithmetic
YearMonth =
YEAR( DimDate[Date] ) * 100 + MONTH( DimDate[Date] )

// YearMonth with FORMAT()
// VALUE() is used to cast the Text string from FORMAT() to a 
// Whole Number
YearMonth =
VALUE( FORMAT( DimDate[Date], "YYYYMM" ) )