Status of a record based at a certain point in time

[lottieritchie]

Hi - hoping someone can help. I've had some assistance already but unfortunately not got to where I need to be. 

I have a table in power BI with records of works that were completed. The records have, amongst other things: 

Created on Date 

Completed on Date 

Status

Eg. 

ID      Created On    Completed On     Status 

1        01/02/2020   10/02/2020       Completed

2        02/02/2020   07/02/2020       Completed

3        05/05/2020   10/05/2020       Completed

4        01/02/2021                            Open

5        02/02/2020                            Open

6        01/02/2020   07/01/2020       Completed

Using the above as an example, I can tell that on today's date I have two 'Open' records. But what I want to see is that on (for example) the same day last year, how many records were 'open' (i.e. were created on on or before that specific date and closed on is after that specific date) at the time.  I want to be able to show this for comparable weeks and years etc. 

I can see from looking at the data that record ID 1 would have been open on the 8th Feb 2020, so the answer is 1, but how do I calculate this in Power BI? 

Thanks for your help. 

[amitchandak]

@lottieritchie , refer if this blog on similar topic can help

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

[lottieritchie]

@amitchandak  

Hi, thank you very much this is very helpful. I have replicated most of these fields, other than I have got stuck on one where it is not liking the second _min_date in my expression: 

(Temp) Last Period Live Jobs =

var _min_date = minx(all(Calendar_Lookup,Calendar_Lookup[Date]),

var _expression =if(ISFILTERED(Calendar_Lookup[Month and Year]),MAXX(Calendar_Lookup,DATEADD(Calendar_Lookup[Date],-1,month)),maxx(Calendar_Lookup,DATEADD(Calendar_Lookup[Date],-1,year)))

Return

CALCULATE(COUNTX(filter(DB_Job_Data,DB_Job_Data[job_CreatedOn]<=_expression && DB_Job_Data[job_CreatedOn]>= _min_date && (Isblank(DB_Job_Data[job_ContractorAdvisedCompletedOn]) || DB_Job_Data[job_ContractorAdvisedCompletedOn]>_expression)),crossfilter(DB_Job_Data[job_CreatedOn],Calendar_Lookup[Date],none))))

Any idea what I am doing wrong here? 

Many thanks for your help.