Reporting date point in time visualisation with data that has a from date and to date

[mcameron2909]

Hi,

I'm trying to work out how to filter my data in Power BI desktop using a disconnected Reporting Date table that shows end of month reporting periods eg.

ReportingDate

30-Jun-20

31-Jul-20

31-Aug-20

30-Sep-20

30-Oct-20

30-Nov-20

31-Dec-20

And when a single reporting date is selected, have my reporting visuals on the page (grids, bar charts) adjust to show just the employees that were currently employed as at the selected reporting date.

Our dataset has all employee records with a StartDate and EndDate (thousands of records). A cutdown version is shown here as an example:

EmployeeID	EmployeeName	State	Division	StartDate	EndDate
1	Joe Smith	NSW	IT	01-Jan-20	04-May-20
2	Kate Dowd	QLD	IT	03-May-20	08-Aug-20
3	Mary Barker	QLD	Sales	06-May-20	NULL
4	Tom Brown	VIC	Sales	08-Sep-20	NULL

They will be active if ReportingDate >= StartDate and ReportingDate <= EndDate

I've seen a similar question in a number of forums where the visuals required have been able to work on a limited number of measures, and the reponse has been to use DAX to generate a measures for each of the required reporting fields using something like this:

Active Employees =
var _currDate=SELECTEDVALUE('Dates'[ReportingDate])
var _employees=
FILTER(
Employee,
( ISBLANK(Employee[EndDate])
|| Employee[EndDate]>=_currDate )
&& Employee[StartDate]<=_currDate // started before that date
)
return COUNTROWS(_employees)

I'm not sure this is not going to work for our requirements - we need to display visuals that have a large number of fields, not just numeric measures but text fields, categories, regions. 

I've also considered using a parameter to load the model for a selected reporting date, but this doesn't provide the user experience required. 

Ideally we can dynamically select any reporting date and see the visuals update on set of employees that were active at that time.

Any help really appreciated!

Michael

[v-eqin-msft · Accepted solution]

Hi @mcameron2909 ,

You could use the following formula to create a measure:

Measure = 
IF (
    ISBLANK ( MAX ( 'Employee'[EndDate] ) )
        || MAX ( 'Employee'[StartDate] )
            <= SELECTEDVALUE ( 'Reporting Date table'[ReportingDate] )
            && MAX ( 'Employee'[EndDate] )
                >= SELECTEDVALUE ( 'Reporting Date table'[ReportingDate] ),
    1,
    0
)

And apply it to filter pane, set as "is 1" like this:

Please take a look at the pbix file here.

Best Regards,
Eyelyn Qin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[mcameron2909]

Thanks Evelyn, that does exactly what I was looking for. Really appreciate your response here and the video respone, thanks!

[DataInsights]

Hi @mcameron2909,

This approach uses an indicator measure that can be used to filter a visual for only active employees. Columns and measures that are added to the visual will be filtered by the indicator measure. The disconnected table ReportingDates does not have any relationships. Let me know if this works for you.

Measure:

Active Employee Indicator = 
VAR vReportingDate =
    SELECTEDVALUE ( ReportingDates[Reporting Date] )
VAR vResult =
    SUMX (
        Employees,
        VAR vStartDate = Employees[StartDate]
        VAR vEndDate = Employees[EndDate]
        RETURN
            IF (
                ISBLANK ( Employees[EndDate] )
                    || ( vReportingDate >= Employees[StartDate]
                    && vReportingDate <= Employees[EndDate] ),
                1
            )
    )
RETURN
    vResult

Filter the visual:

[DataInsights]

See revised measure below:

Active Employee Indicator = 
VAR vReportingDate =
    SELECTEDVALUE ( ReportingDates[Reporting Date] )
VAR vResult =
    SUMX (
        Employees,
        VAR vStartDate = Employees[StartDate]
        VAR vEndDate = Employees[EndDate]
        RETURN
            IF (
                ISBLANK ( vEndDate )
                    || ( vReportingDate >= vStartDate
                    && vReportingDate <= vEndDate ),
                1
            )
    )
RETURN
    vResult

[mcameron2909]

Thanks for your response - Evelyn also responded with effectively the same solution, really appreciate you looking at this and coming back with the solution, looks to work, cheers.