Forum Discussion

Fro88er's avatar
Fro88er
Helper IV
4 years ago
Solved

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's avatar
    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.
     
    Now please help me with kudos and click the thumbs up and accept as solution button. Thanks 😎
     
speedramps's avatar
speedramps
Super User
4 years ago

Thank you Fro88er for accepting the solution. 😁😁😁

I had a look and think that the FactJobs table is not required because all the fields are in FactJobSubmission.

Raise another ticket if you need help resoving the the Fact to Fact relatioinship and quote speedramps in the ticket. That will automtaically send me an notification but another solver might get to you first.

Good luck ! 😎