Counting rows where date column falls within the last year

[dusdau]

Hi All,

DAX newbie here, trying to do what seems like it should be a simple measure.  I have a table ('Market Share') that has a column of dates (LastModified), which has a relationship to my date table ('Dates'). Date is my date key column. I simply want to count the rows in the Market Share table where the LastModified column is within the last year.

It seems like this should do the trick, but its not returning anything:

UpdatedLastYear = CALCULATE(COUNTROWS('Market Share'), DATESBETWEEN('Dates'[Date],NEXTDAY(SAMEPERIODLASTYEAR(LASTDATE('Dates'[Date]))),LASTDATE('Dates'[Date])))

Can anyone tell what I'm missing?

Thanks,

Dustin

[Anonymous]

UpdatedLastYear = CALCULATE(
	COUNTROWS('Market Share'),	
	DATESINPERIOD(
		'Dates'[Date], 
		TODAY(), 
		-12, 
		MONTH
	)
)

Give this a try

[dusdau]

Thanks Ross....same thing though...not returning anything

[Anonymous]

Can you try this for me:  Lets separate the countrows out into its own measure. So you have:

CountMeasure = COUNTROWS('Market Share')

UpdatedLastYear = CALCULATE(
	[CountMeasure],	
	DATESINPERIOD(
		'Dates'[Date], 
		TODAY(), 
		-12, 
		MONTH
	)
)

[dusdau]

Thanks again Ross.  The count measure is working fine, but there must be something wrong with the way the measure is evaluating dates as that part is not working.  It doesn't return anything.  I realized my Dates table was not marked as a date table, but marking that didn't fix it.

Could the fact that some of the LastModified dates in the Market Share table are NULL cause this issue?

Thanks so much for your help on this.

Dustin

[Anonymous]

The nulls shouldn't matter that much.  Your column is set to type "Date" isnt it?

[dusdau]

Interestingly the column or nulls seems to have an issue.   It is definitely a Date/Time column.

I created another column with formula (TodayDate = TODAY()-325) and then changed the relationship to the date table, and the formula works.

Not quite sure how to deal with the NULLs yet...

[Anonymous]

Good info to know!  Glad you are getting closer.  You are on the right track.  A find/replace of nulls to a specific date could be your solution. Whether that date is done via a lookup, or as a set "filter me out" type date will depend on your needs.

[dusdau · Accepted solution]

Ok, it wasn't the null values.  I think it was an issue with Date/Time format of the column.  I changed both the Market Share table and the Date table to 'Date' and it works.  Thanks for your help Ross!