Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago
Solved

Dates and relationships

Hi, I want to count the number of assignments id's based on start date and end date.   My table is like this:   Relationships:   Measures: Assignments Added by StartDate = CALCU...
Need Help
  • tex628's avatar
    tex628
    7 years ago

    Your measures are currently calculating the total number of assignments that has a specific startdate/enddate. If you place them both in cards without applying any filters, they will give you the exact same value as every assignment you have will include both start and enddate. 

Anonymous's avatar
Anonymous
Not applicable
7 years ago

Thanks tex628 , now I understood :)

 

Do you also know how I can count assignmentsid and filter on  startdate is greater less than today and end date is greater than today?

tex628's avatar
tex628
Icon for Community Champion rankCommunity Champion
7 years ago

Do i understand you correctly that you want to count the total number of assignments that are currently active? (Started but not ended)

  • Anonymous's avatar
    Anonymous
    Not applicable
    7 years ago

    tex628 Yes :)

    I was thinking this should work, but it gave me me 113, and it should have been over 200

     

    candicates on assignments = CALCULATE(DISTINCTCOUNT(Assignments[CandidateId]); FILTER(Assignments; Assignments[StartDate]<TODAY()&& Assignments[EndDate]>TODAY()))

    Or this one returns 113 as well:

    candicates on assignments = CALCULATE(DISTINCTCOUNT(Assignments[CandidateId]);Assignments[StartDate] < TODAY(); Assignments[EndDate] > TODAY())
    • tex628's avatar
      tex628
      Icon for Community Champion rankCommunity Champion
      7 years ago

      Those measures look like they should work ... 

      Create a normal table, assignment , startdate & enddate then add the measure at the end. You should be able to tell which assignments are included as they should get a "1". 

      Find a row that should have a "1" but hasn't and we'll go from there and figure out what's wrong!

      / J

      • Anonymous's avatar
        Anonymous
        Not applicable
        7 years ago

        Hi,tex628 

        I made the table and found some that should have been one:

        I'm suspecting that I need to use "userelationship" to get this to work, and I've been experimenting a bit with it, but haven't figured it out yet. I also tried to replace candidateid with assignment which gave me a bit higher number, but not correct yet.