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 3...
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.

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.