Filter Historical Data

[Unknowncharacte]

Hi, I need to create a measure to calculate # of all cases (regardless of date opened) that were active (no closed date) at the end of each fiscal quarter and display the measure on a historical line chart. I have a Calendar table and I need to visualize the measure on it because it has fiscal year information but I am getting confused on how to capture all cases that were active. 

I did this measure, but it's counting cases that were opened in that quarter, I assume because I have a direct connection from fact table to dim calendar. How do I count and visualize this, I am confused...

Open Cases =
COUNTROWS( FILTER( ALL('Fact Table'),
[DateOpened] <= MAX('DIM Calendar'[Full Date])
&& [DateClosed] >= MAX('DIM Calendar'[Full Date]) ) )

Attaching a sample of my Power BI as well. 

Sample Data.pbix

[Unknowncharacte]

I did this measure, but it's counting cases that were opened in that quarter, I assume because I have a direct connection from fact table to dim calendar. How do I count all "active" cases?

Open Cases = COUNTROWS( FILTER( ALL('Fact Table'), [DateOpened] <= MAX('DIM Calendar'[Full Date]) && [DateClosed] >= MAX('DIM Calendar'[Full Date]) ) )

[amitchandak · Accepted solution]

@Unknowncharacte , You have to use cross filter to remove the join

refer, blog video, or attached files after signature

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

[Unknowncharacte]

Thank you so so much! Active employee count worked perfectly for me!