How to keep existing filters while using calculate

[mickey]

Hi,

I have a table A which has the following fields: Episode, Start Date End Date

I have created another calculated table B which has a list of dates and I am trying to pull from table A how many episodes have a start date <= date in table B and a end date > date in table B.

I tried the following calculation:

Count =

VAR TableADate = 'TableA'[Date]

RETURN

    CALCULATE(

        COUNTROWS('TableA'),

        FILTER('TableA',

        'TableA'[Start Date] <= TableA

        && 'TableA'[End Date] > TableA     

    )

Though the above calculation is not working with a slicer I have for Table A. 

Thanks for the help.

[Ashish_Mathur]

Hi,

You should be writing a measure (not a calculated table formula).  Share some data in a format that can be pasted in an MS Excel file and show the expected result.

[amitchandak]

@mickey , A slicer value can not be used in a calculated table

Consider these measure ways

Power BI: HR Analytics - Employees as on Date : https://youtu.be/e6Y-l_JtCq4
https://community.powerbi.com/t5/Community-Blog/HR-Analytics-Active-Employee-Hire-and-Termination-tr...

Measure way
Power BI Dax Measure- Allocate data between Range: https://youtu.be/O653vwLTUzM