Forum Discussion

michellefs_br's avatar
michellefs_br
Helper I
10 years ago

Measure Based on Dates

Hi All,   I'm trying to create a measure that will COUNT or SUM the continuing people on a course. For example, table bellow:   Trainee 1 Trainee 1 singed up to programme with a Participation st...
Anonymous's avatar
Anonymous
Not applicable
10 years ago

I have a similar situation in several of my reports and this looks like a better solution than what I've been doing. However this measure gives me an error:

 

Error Message:

MdxScript(Model) (14, 4) Calculation error in measure 'locks'[Continuing]: An invalid numeric representation of a date value was encountered.

Stack Trace:



Invocation Stack Trace:



Activity ID
90f6fde6-85be-4097-ed89-742c2438c001

Time
Wed May 11 2016 10:01:04 GMT-0400 (Eastern Daylight Time)

Version
2.34.4372.322 (PBIDesktop)

I removed the ADDCOLUMNS piece of your measure because I only have one end date to contend with, so no need to pick between two options there. Otherwise I believe I've done everything else identically to yours. Any idea what to do about that error?

 

Continuing = 
SUMX (
	FILTER (
		GENERATE (
			CALCULATETABLE (
				SUMMARIZE (
					locks,
					locks[startdate],
					locks[enddate],
					"Rows", COUNTROWS ( locks )
				),
				ALL ( DateTable )
			),
			DATESBETWEEN ( DateTable[Date], locks[startdate], locks[enddate] )
		),
		DateTable[Date] = MIN ( DateTable[Date] )
	),
	[Rows]
)
OwenAuger's avatar
OwenAuger
Super User
10 years ago

Not sure...

 

I googled that error and a few results came up like this one from March 2015:

https://social.msdn.microsoft.com/Forums/sqlserver/en-US/9b9bc00b-9846-43a4-845a-ea0e98fbf327/datesbetween-an-invalid-numeric-representation-of-a-date-value-was-encountered

 

It seems that in some version of DAX, DATESBETWEEN could return this error when date arguments don't appear in the date table, but I can't reproduce the error in Power BI Desktop.

 

I see you're using the latest version of Power BI Desktop (2.34) as well.

 

Could you provide a link to a santisied model that produces the error?

  • Anonymous's avatar
    Anonymous
    Not applicable
    10 years ago

    Here we see the dangers of recycling.

     

    My generic date table that I use in most reports runs from 2010-2020. It doesn't actually map all that well to the particular dataset I'm testing this on, because there are start dates as early as 2000. I never altered the date table because I only needed to report after 2010, but this formula doesn't share my lazy attitude toward filtering. I changed the date table's range and it works perfectly.