Filter two dates and userelationship

I am having an issue filtering Client Submits on Jobs using ONLY the Job date added. We have two tables. Jobs and Client Submits. JobID and when it was date added, then Clients submitted to thos...

Need Help

speedramps
4 years ago

If you want the total of jobs started and submitted in the same mdate range then

create an active 1:M relationship from Calendar[date] to yourtable[Submit date].

Then assuming that submit dates cant be less that the Job date,

create this measure ....

Added and Submitted =

// get max calendar dates for the context

VAR maxdate = MAX('Calendar'[Date])

//create subset of records in the date range

VAR mysubset = FILTER( yourtable, yourtable[Submit date] <= maxdate)

RETURN

// get total for subset

COUNTROWS(mysubset)

Drag the calender date to your slicer or the table visual (dont use the yourtable dates).

Then drag Added and Submitted to your table visual

Here is an example using Order Date and Despatch Date with inactoive relationships but the logic is exactly the same as Job Added and Client Submitetd date. Plus it is much better for me to teach you how to DIY, rather than just build a solution.

Click here to download an example

Now please help me with kudos and click the thumbs up and accept as solution button. Thanks 😎

Forum Discussion

Filter two dates and userelationship

Related content

USERELATIONSHIP() - Visual Filter

USERELATIONSHIP with Filters

RLS and USERELATIONSHIP problem

USERELATIONSHIP problems with n:m-relationship

Userelationship for multiple dates