Measure Based on Dates

Question

Hi All,&nbsp;I'm trying to create a measure that will COUNT or SUM the continuing people on a course. For example, table bellow:&nbsp;Trainee 1Trainee 1 singed up to programme with a Participation start date 01/02/2015In May Trainee 1 withdraw with actual end date of 15/05/2015Trainee 1 then signed up to another programme end of May with a Participation start date of 30/05/2015Trainee 1 then completed programme with actual end date of 21/11/2015&nbsp;Month Signups Continuing Completions Withdrawals&nbsp;&nbsp; NetJan&nbsp;&nbsp;&nbsp;&nbsp; 0&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 0&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 0 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;&nbsp; 0&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 0Feb&nbsp;&nbsp;&nbsp; 1 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;&nbsp; 1&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 0&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 0 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;&nbsp; 1Mar&nbsp; &nbsp;&nbsp; 0&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 1 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;&nbsp; &nbsp; 0&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &nbsp;&nbsp; 0 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;&nbsp; 0Apr &nbsp; &nbsp; 0&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 1 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;&nbsp; &nbsp; 0&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 0&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 0May&nbsp;&nbsp;&nbsp; 1&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 1 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;&nbsp; 0&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 1 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 0Jun &nbsp;&nbsp;&nbsp; 0&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 1&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 0&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 0&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 0Jul&nbsp;&nbsp; &nbsp;&nbsp; 0&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 1&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 0&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &nbsp; 0&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 0Aug&nbsp;&nbsp;&nbsp; 0&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 1&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 0&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 0&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 0Sep&nbsp;&nbsp;&nbsp; 0&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 1&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 0 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 0 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 0Oct&nbsp;&nbsp;&nbsp;&nbsp; 0&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 1&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 0&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 0 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;&nbsp; 0Nov&nbsp;&nbsp;&nbsp;&nbsp; 0&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 1&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 1 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;&nbsp; 0&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; -1Dec&nbsp;&nbsp;&nbsp;&nbsp; 0 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;&nbsp; 0 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;&nbsp; 0&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 0&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 0&nbsp;&nbsp;&nbsp;I have the start date and end date, and that is how I filter the date signups and completions, but still unsure how to have the continuing ones, any advice is much appreciated

owenauger · Answer

Hi there,&nbsp;The "Continuing" measure can be modelled as&nbsp;a variation on 'events in progress', but in your case you want to count&nbsp;how many&nbsp;programmes are in progress at a point in time that were already in progress earlier.&nbsp;(See this paper for events in progress: http://www.sqlbi.com/wp-content/uploads/DAX-Query-Plans.pdf)&nbsp;I have created an&nbsp;example model that you&nbsp;can play with, by modifying the code on page 27 of the above paper :)PBIX file here:https://www.dropbox.com/s/qnfv05chw49692w/Events%20continuing%20on%20date.pbix?dl=0&nbsp;The exact code for your situation depends on how your data model&nbsp;is structured - I have created dummy tables that I think capture your situation (using the example dates you gave).The logic of the code&nbsp;depends on how you want to define "Continuing".I have assumed someone is continuing in a programme if they began the programme before the current time period and are still in the programme on the first day of the current time period.Note: I don't show any Continuing values in Feb&nbsp;because no-one was in a programme before Feb. You can modify this if you want.The "Continuing" measure looks like this (using the code from the SQLBI paper as a starting point):&nbsp;Continuing = 
SUMX (
    FILTER (
        GENERATE (
            ADDCOLUMNS (
                CALCULATETABLE (
                    SUMMARIZE (
                        CourseData,
                        CourseData[Signup Date],
                        CourseData[Withdrawal Date],
                        CourseData[Completion Date],
                        "Rows", COUNTROWS ( CourseData )
                    ),
                    ALL ( 'Date' )
                ),
                "EndDate", MAX ( CourseData[Withdrawal Date], CourseData[Completion Date] )
            ),
            DATESBETWEEN ( 'Date'[Date], CourseData[Signup Date] + 1, [EndDate] )
        ),
        'Date'[Date] = MIN ( 'Date'[Date] )
    ),
    [Rows]
)The measure works by:Taking all date combinations from the CourseData table and counting rows (SUMMARIZE)GENERATE-ing&nbsp;a list of dates between the start date and end date (either withdrawal or completion), but omitting the start date itself (this is the boundary condition: we don't want to count courses starting on the start date of the period)If this list of dates contains the first date of the current period (FILTER), then the number of rows is counted (SUMX)All the best,Owen :)&nbsp;

anonymous · Answer

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:&nbsp;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?&nbsp;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 · Answer

Not sure...&nbsp;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&nbsp;It seems that in some version of DAX,&nbsp;DATESBETWEEN could return this error&nbsp;when date arguments don't appear in the date table, but I can't reproduce the error&nbsp;in Power BI Desktop.&nbsp;I see you're using the latest version of Power BI Desktop (2.34) as well.&nbsp;Could you provide a link to a santisied model&nbsp;that produces the error?

asocorro · Answer

Could you please clarifry what it is you are trying to calculate?

michellefs_br · Answer

I'm trying to calculate the CONTINUING column, as that's the one I don't have on my tables

Forum Discussion

Measure Based on Dates

6 Replies

Related content

Measure based on latest date

DAX - Average (measure) based on most recent Date

Unable to create Date Dimension and Date related measures

Dynamic Filtering based on date selection

Measure based on Today's date