Measure Based on Dates

[michellefs_br]

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 start date 01/02/2015
In May Trainee 1 withdraw with actual end date of 15/05/2015
Trainee 1 then signed up to another programme end of May with a Participation start date of 30/05/2015
Trainee 1 then completed programme with actual end date of 21/11/2015

Month Signups Continuing Completions Withdrawals   Net
Jan     0            0                 0                  0                    0
Feb    1            1                 0                  0                    1
Mar     0           1                  0                  0                    0
Apr     0           1                  0                 0                      0
May    1           1                  0                  1                     0
Jun     0           1                  0                  0                     0
Jul      0            1                 0                  0                      0
Aug    0            1                0                    0                    0
Sep    0            1                0                   0                     0
Oct     0            1               0                   0                      0
Nov     0            1              1                    0                     -1
Dec     0            0              0                   0                      0

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

[asocorro]

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

[michellefs_br]

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

[OwenAuger]

Hi there,

The "Continuing" measure can be modelled as a variation on 'events in progress', but in your case you want to count how many programmes are in progress at a point in time that were already in progress earlier.

(See this paper for events in progress: http://www.sqlbi.com/wp-content/uploads/DAX-Query-Plans.pdf)

I have created an example model that you 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

• The exact code for your situation depends on how your data model is structured - I have created dummy tables that I think capture your situation (using the example dates you gave).
• The logic of the code 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 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):

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:

1. Taking all date combinations from the CourseData table and counting rows (SUMMARIZE)
2. GENERATE-ing 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)
3. 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 🙂

[KHorseman]

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]

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/datesb...

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?

[KHorseman]

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.