Filter fiscal year data based on two date columns

[Krishna09]

Hello all,

In Table 1, there are two date fields(one is start and expiration date) and one ID field. 

I have a other table with calender date, month, year, fiscal year columns. 

I need to create a filter, based on selection of fiscal year , it should show ID's which are in the range of start and expiration date.

Is there a way or any alternate way. Please suggest me.

[Anonymous]

Hi @Krishna09 ,

You will need an unconnected fiscal table with a start date and end date for a fiscal year:

Then create a measure like:

Measure = 
VAR _start_date = MAX('FYCalendar'[StartDate])
VAR _end_date = MAX('FYCalendar'[EndDate])
VAR _result = IF(ISBLANK(SELECTEDVALUE('FYCalendar'[FiscalYear]))||(_start_date>=MAX('Table'[Start Date]) && _end_date<=MAX('Table'[Expiration Date])),1)
RETURN
_result

And the output:

Best Regards,
Gao
Community Support Team

If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly.
If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

How to get your questions answered quickly --  How to provide sample data in the Power BI Forum

[Krishna09]

@Anonymous , @amitchandak ,

Thank you both.

is it possible to show only the row based on ID, with out measure count in the table?

[Anonymous · Accepted solution]

Hi @Krishna09 ,

You can simply add it to the visual's filter and set the condition equal to 1.

Best Regards,
Gao
Community Support Team

[Krishna09]

Hello @Anonymous , 

I used the formulae you mentioned, but it works only when I use in table filter.

--- it is not working if I mention it, then the table is filtered only based on start date. 

--- AND it is also not working in pie or other visuals.

I have multiple tables connected to this visuals.

Below is the experssion I am using.

ContractExp_year = CALCULATE(COUNTX(filter('ContractExpiration','ContractExpiration'[[PCW]]Contract (Effective Date)]<= MAX(FiscalYearfFromOctober[Date])&&

'ContractExpiration'[[PCW]]Contract (Expiration Date)]> MAX(FiscalYearfFromOctober[Date])),'ContractExpiration'[[PCW]]Contract (Contract Id)]),

CROSSFILTER(FiscalYearfFromOctober[Date],'ContractExpiration'[[PCW]]Contract (Effective Date)],None))

[amitchandak]

@Krishna09 , I think smimilar to HR Analytics

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...
Power BI HR Active Employee Tenure Bucketing, and Hired, Terminated, and Active employees: https://youtu.be/fvgcx8QLqZU