Measure Based on Dates

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 :)