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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
lottieritchie
Helper I
Helper I

Status of a record based at a certain point in time

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. 

2 REPLIES 2
amitchandak
Super User
Super User

@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. 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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