DAX - calculate a measure based on a range relationship

[Anonymous]

Hello Experts,

I have a transactional table and a calendar table as below:

I need to find the number of jobs overdue (i.e.after scheduled date) as on 09-Mar-2021.

so basically looking at my calendar table, i would need all the 3 jobs from the Jobs table.

But i cannot prorate the date and create a relationship between the calendar table as prorating is causing the data size to grow exponentially and i end up having 10 M rows. 

As per the current construct, these are unrelated tables.

With Treatas i could form a relationship but that would work on equality between columns.

What i require is to filter the Jobs table based on the Calendar table using the condition :

include jobs only when Calendar date is between scheduled date and completed date.

Request your inputs in the DAX calculation for the same.

Thanks in advance.

Kind regards,

Chetan

[v-stephen-msft]

Hi @Anonymous ,

Create the following measure. Put the measure into Filters and set as follows. In addition, select the filter type as TOPN in the Person Name field of the Filters, drag the JobNo field into the value, and select Count.

Measure = IF(MAX('Jobs'[CompletedDate])>MAX('Jobs'[ScheduledDate])&&MAX('Jobs'[ScheduledDate])<=SELECTEDVALUE('Calendar'[Date]),1)

The relationship among the three tables is as follows:

You can check more details from the attachment.

Best Regards,

Stephen Tao

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

Hi @v-stephen-msft ,

The scenario is a bit complicated here with Person being a dimension and only containing the Person details and no job details. Also, it has a many to one relationship with Jobs over PersonId.

There is one more complication to this, that the slicer is a range slicer and not a specific date selection.

[v-stephen-msft · Accepted solution]

Hi @Anonymous ,

For the range slicer, use MAX('Calendar'[Date]) and MIN('Calendar'[Date]) to target this date range.

Measure =
IF (
    MIN ( 'Calendar'[Date] ) <= MAX ( 'Jobs'[ScheduledDate] )
        && MAX ( 'Jobs'[ScheduledDate] ) < MAX ( 'Jobs'[CompletedDate] )
        && MAX ( 'Jobs'[ScheduledDate] ) <= MAX ( 'Calendar'[Date] ),
    1
)

Can you share with me the complete sample data? It does not need to be real data, just a similar data model, and give the expected results you want.

Best Regards,

Stephen Tao

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[lbendlin]

How is the March 9 cutoff defined?  By a user slicer?

[Anonymous]

Hi @lbendlin ,

Right. The cut-off is defined by a range slicer which has start and end date.

Also to add to this requirement, i have one more table Person which has Person Name and Job Id; i need to find top 5 Persons with most overdue jobs for a selected period. 

Kind Regards,

Chetan A.