Expression for SUM total to a max date in a table (that updates daily)

[rhaddad87]

Hello,

I currently use this formula to determine the number of PoP Hours depending on the date selected.  I have to go in and change the date on the slider on a daily basis for it to update to today's information.

PoP Hours = SUM(dimSites[Daily Max Hours])*COUNTROWS(dimDate)

I'd like to produce a formula that will only count the number of rows from dimDate to the MAX date in 'xxData'[Date] so that I don't have to change the date everyday in the slider, I can leave it on a future date and the formula will auto update depending on the latest date in 'xxData'.  I can not count the rows in 'xxData'[DATE] because there are multiple entries per day

Thank you! 

[bcdobbs · Accepted solution]

Direct replacement of what you have is

PoP Hours = 

VAR LatestDate = MAX('xxData'[Date])

VAR NumberOfDates = 
	CALCULATE(
		COUNTROWS(dimDate),
		dimDate[Date] < LatestDate
		)

RETURN
SUM(dimSites[Daily Max Hours])*NumberOfDates

However do you really want a count of dates in date table or are you really interested in just dates that exist in xxData in which case:

PoP Hours = 

VAR DatesInTable = 
	VALUES ( 'xxData'[Date] )

VAR NumberOfDates = 
	COUNTROWS ( DatesInTable )

RETURN
	SUM(dimSites[Daily Max Hours])*NumberOfDates

[rhaddad87]

oh wow, not only did you fix my problem with my solution, you showed me the benefit of using the correct function, VALUES.  Thank you so much!!!  This is so helpful!