Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Enhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.

Reply
leekep88
Frequent Visitor

Count based on a date in another table

I feel like this should be a simple equation, but I am new to Power BI and am still learning DAX. I have read other solutions on similar questions, but I can't get anything to work in my table. My 'Raw Data' table has ticket data with Pay Period references, Created Dates and Closed Dates. I have another reference table 'Pay Periods' which just has all of the Pay Periods with the associated Start and Stop Dates, as well as a Back Log Date which is just 12 weeks prior to the start date.

 

I am building a Matrix that has all of the Pay Periods with counts of actions created, closed, etc. The column that I am having difficulty with is the Back Log column. All I want to do is count the number of actions that have a 'Raw Data' [Create Date] that is earlier than 'Pay Period'[Back Log Date]. When I use the filter function, it won't let me reference two different tables. I've been stuck on this for two weeks, please help.

2 REPLIES 2
amitchandak
Super User
Super User

@leekep88 , Check if you are looking for active employee kind of measure

https://community.powerbi.com/t5/Community-Blog/HR-Analytics-Active-Employee-Hire-and-Termination-tr...

 

file are attached after signature

Share with Power BI Enthusiasts: Full Power BI Video (20 Hours) YouTube
Microsoft Fabric Series 60+ Videos YouTube
Microsoft Fabric Hindi End to End YouTube

I tried an iteration of it, but it returned blanks in all of the columns. I don't really understand the logic of it enough to translate it to my situation. 

 

Back Log = CALCULATE(COUNTX(FILTER('Raw Data','Raw Data'[CREATEDATE]<=max('Pay Period'[Back Log Date])),'Raw Data'[PP CREATE]),CROSSFILTER('Raw Data'[CREATEDATE],'Pay Period'[Back Log Date],None))

Helpful resources

Announcements
July 2025 community update carousel

Fabric Community Update - July 2025

Find out what's new and trending in the Fabric community.

July PBI25 Carousel

Power BI Monthly Update - July 2025

Check out the July 2025 Power BI update to learn about new features.