cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
CMDATA
Helper I
Helper I

Filtering records based on a record date within another record's start and end date

Hello!

 

I am working with large data sets from our companies ERP system so I'm simplifying the issue a bit but basically we have a list of jobs and those jobs each have a start date and an end date. I need to filter the jobs shown on the dashboard by picking a date and only showing the jobs that are still "open" meaning the date chosen is on or in between their start and end dates. Jobs without an end date that will be blank. Tied to those jobs are records that need to be created daily. Those records have a header record with a date and detail records. I would also like to be able to show jobs that have a record for that date, even if the job is not started yet or if it has already ended. 

 

What i would like to be able to do is for a given single date from the header record (or related date table), pull the daily records for those jobs (which there could be multiple) and show which jobs had a record, then show all of the jobs that dont have record but should have given their start and end dates. 

 

Expected outcome based on the data would be a dashboard that is defaulted to yesterday's date using a bookmarked filter and that will filter the number of jobs to show the total number of jobs open on that date, the total number with a record, the total number without a record, and then aggregate the data from the records in various ways like total # of workers, total internal worksers, total external workers, number of closed records, and number of pending records. There are also other tables like key job personnel that would pull in as well. Then another button will clear that filter and the user can select any date in the past. Basically the date selected should filter the job table and then the job table should filter everything else. 

 

I have this working based on static job statuses, but we need to be dynamic so that if i look at yesterday's date it shows the jobs that were active as of yesterday and if i look at 2 weeks ago or 2 months ago it will show different jobs becuase some will have ended and others will have started. 

 

The two items I'm struggling with are how to dynamically filter the jobs shown to only those open based on the date selected and their start and end dates while also show all jobs that have a record, and all jobs that should have a record but don't. And ideally showing them all in one table with some summary information in cards along the top. 

 

The users of this will then filter that table (or it will be pre-filtered by RLS for them) to follow up on jobs missing records or with pending records on a given date or see aggregate data for a specific date for workforce. 

 

Any insight into how to get this thing to filter like this would be greatly appreciated! 

 

Using the data below if i set a date slicer for 6/4/2021 I should see a table like this. 

DETAIL TABLE

Job Number of recordsNumber of closed recordsNumber of Pending recordsNumber of Internal WorkersNumber of External WorkersJob Manager
100     Peter Cottontail
200211 5John Smith
300     Jane Doe

 

If I set it for 6/9/2021:

DETAIL TABLE

Job Number of recordsNumber of closed recordsNumber of Pending recordsNumber of Internal WorkersNumber of External WorkersJob Manager
1001 15 Peter Cottontail
200     John Smith
3001 125Jane Doe
40011 75Mike Jones

 

 

 

 

SAMPLE DATA

Job Table

Job CodeJob NameStart DateEnd Date
100Job 16/1/20216/5/2021
200Job 26/2/20216/10/2021
300Job 36/3/2021 
400Job 46/8/2021 

 

Record Header

Job CodeRecord Header System CodeRecord DateRecord Status
100683216/1/2021Closed
100685486/2/2021Closed
100693456/9/2021Pending
200682346/2/2021Closed
200691276/4/2021Closed
200695346/4/2021Pending
200696526/6/2021Closed
200697586/6/2021Pending
200698436/7/2021Closed
200699846/7/2021Pending
300697126/6/2021Closed
300699956/9/2021Pending
400725686/9/2021Closed
400735686/15/2021Pending

 

Record Detail

Job CodeRecord Header System CodeRecord Detail System CodeCompany Name# of workers
1006832168321.1Internal 17
1006832168321.2External 28
1006832168321.3External 32
1006832168321.4External 45
1006854868548.1Internal 14
1006854868548.2External 26
1006854868548.3External 35
1006854868548.4External 48
1006934569345.1Internal 15
2006823468234.1External 12
2006912769127.1External 13
2006953469534.1External 12
2006965269652.1External 13
2006975869758.1External 15
2006984369843.1External 12
2006998469984.1External 11
3006971269712.1Internal 12
3006971269712.2External 35
3006999569995.1Internal 12
3006999569995.2External 35
4007256872568.1Internal 1

7

4007256872568.2External 45
4007356873568.1Internal 18

 

Key Personnel

JobName
100Peter Cottontail
200John Smith
300Jane Doe
400Mike Jones

 

 

1 ACCEPTED SOLUTION

Doesn't seem to be working as I expect. I was able to solve this on my own using an answer to one my past questions on this forum. Using this measure and date table that goes from 5 years ago to today:  

Active Jobs =
var __lastVisibleDate = MAX( 'Date Table'[Date] )
var __result =
CALCULATE(
COUNTROWS( 'Job Table' ),
KEEPFILTERS('Job Table'[Start Date] <= __lastVisibleDate ),
KEEPFILTERS(
     OR(
     'Job Table'[End Date] > __lastVisibleDate,
     ISBLANK('Job Table'[End Date])
)
)
)
return
__result
 
This give me a count of jobs that are active against a give date on that date table. And if i show a single job on a table then this will show a 1 if that job is active and blank if it is not.
 
Then i linked the Record header table to that so it also filters down the data to jobs with records on that date. 
 
Then i created a summary table from the jobs and records by date and status
 
Then I have two other measures: 
This looks at that summary table and gives me an indicator if the job exists on that table or not but only looks if the job is Active. 
Record Created =
IF (AND('Table'[Active Jobs]=1, NOT ( MAX ( 'Job Table'[Job Number] ) ) IN VALUES ( 'Record status by job and day'[Job Number] )),
"N","Y")
This gives me a count of jobs if a job has no record on a given date. 
Jobs with No dailies = CALCULATE(countx('Job Table',[Record Created]),filter('Job Table',[Daily Journal Created]="N")) 
 
Then i displayed this in two tables. One showing me jobs with records on the date selected and one showing me jobs that were active on that date and should have a record but do not. 
 
pardon the massive redactions:
CMDATA_0-1635282294089.png

 

 

View solution in original post

8 REPLIES 8
speedramps
Solution Sage
Solution Sage

Hi CMDATA

 

Thanks your example data but I dont quite understand all your explantion.

 

I have therefore built the attached PBIX so we can try work together to solve your problem.

 

Click on Edit Queries and note I have created a Calandar table and denormallised your tables all into one FACT table.

I appreciate you may not wnat to normalise you large ERP data in the final solution,

but this denormallised table will greatly help simplify the problem and better understand your maths.

We can then then use relationships in the final solution.

i.e lets walk before we can run !

 

Please can you explain in full detail your maths / logic for job 400 for a silcer date = 6/9/2021.

 

How do you get an answer of 1 please? 

 

Click here to download PBIX 

 

Capture.JPG

Thank you so much for helping me with this! 

 

This comes out as 1 becuase there is only 1 header record for 6/9/2021 for job 400. On your flat table it would be a distinct count of header record by job. 

Hi again CMDATA

 

Thanks for the additional infomation, I think I understand now.

 

Click here for the an updated solution 

 

Note I have used Edit Queries to denormalise the data.

This make it easier to undestand and might be more efficient than having lots of releationships.

 

In the Field well, expand the DAX measures and look how I have created each measure.


Remember we are BI community voluntrees so please click the thumbs-up for me taking the trouble to help you and then accept the solution if it works.  Thank you !

 

 

Its almost there but not quite. The missing piece is that the date selection should be all days and the report table should show both jobs with records and jobs without records as long as the job without records was active on the date selected. 

 

CMDATA_0-1635182513477.png

 

Hi again CMSDATA

 

Click here to see demo solution 

 

I have added a calendar slicer with a relationship to the fact table.

Have a look at the edit queries, relationships and dax measures.

 

Remember I don't get paid, we are BI community voluntrees so please click the thumbs-up for me taking the trouble to help you and then accept the solution if it works.  Thank you !!!

Hello. Thank you so much again for working on this. That link is not working for me. your previous link worked just fine. 

Sorry about that CMDATA, please try this link  ...

 

Click here to download a demo solution 

Doesn't seem to be working as I expect. I was able to solve this on my own using an answer to one my past questions on this forum. Using this measure and date table that goes from 5 years ago to today:  

Active Jobs =
var __lastVisibleDate = MAX( 'Date Table'[Date] )
var __result =
CALCULATE(
COUNTROWS( 'Job Table' ),
KEEPFILTERS('Job Table'[Start Date] <= __lastVisibleDate ),
KEEPFILTERS(
     OR(
     'Job Table'[End Date] > __lastVisibleDate,
     ISBLANK('Job Table'[End Date])
)
)
)
return
__result
 
This give me a count of jobs that are active against a give date on that date table. And if i show a single job on a table then this will show a 1 if that job is active and blank if it is not.
 
Then i linked the Record header table to that so it also filters down the data to jobs with records on that date. 
 
Then i created a summary table from the jobs and records by date and status
 
Then I have two other measures: 
This looks at that summary table and gives me an indicator if the job exists on that table or not but only looks if the job is Active. 
Record Created =
IF (AND('Table'[Active Jobs]=1, NOT ( MAX ( 'Job Table'[Job Number] ) ) IN VALUES ( 'Record status by job and day'[Job Number] )),
"N","Y")
This gives me a count of jobs if a job has no record on a given date. 
Jobs with No dailies = CALCULATE(countx('Job Table',[Record Created]),filter('Job Table',[Daily Journal Created]="N")) 
 
Then i displayed this in two tables. One showing me jobs with records on the date selected and one showing me jobs that were active on that date and should have a record but do not. 
 
pardon the massive redactions:
CMDATA_0-1635282294089.png

 

 

View solution in original post

Helpful resources

Announcements
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

November Power BI Update 768x460.png

Check it Out!

Click here to read more about the November 2021 Updates!

M365 768x460.jpg

Microsoft 365 Collaboration Conference | December 7–9, 2021

Join us, in-person, December 7–9 in Las Vegas, for the largest gathering of the Microsoft community in the world.