Forum Discussion

mcameron2909's avatar
mcameron2909
Frequent Visitor
5 years ago
Solved

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

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:

 

EmployeeIDEmployeeNameStateDivisionStartDateEndDate
1Joe SmithNSWIT01-Jan-2004-May-20
2Kate DowdQLDIT03-May-2008-Aug-20
3Mary BarkerQLDSales06-May-20NULL
4Tom BrownVICSales08-Sep-20NULL

 

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

Need Help
  • Anonymous's avatar
    Anonymous
    5 years ago

    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.

5 Replies

  • DataInsights's avatar
    DataInsights
    Icon for Super User rankSuper User
    5 years ago

    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's avatar
      DataInsights
      Icon for Super User rankSuper User
      5 years ago

      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's avatar
        mcameron2909
        Frequent Visitor
        5 years ago

        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.

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    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's avatar
      mcameron2909
      Frequent Visitor
      5 years ago

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