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.

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!