Forum Discussion

HKV's avatar
HKV
Frequent Visitor
5 years ago

Create measure to calculate tasks based on timetable

Hi, i hope you can help as i have no clue how to solve this.

 

I have 3 tables, Consultant table, Consultant Job and a Calendar table which contains all dates and times.

Relationships are this:

https://imgur.com/jT5X4jZ

 

A consultant can have many tasks. Each task has a start and a end date. I want to show how many consultants we have hired in each month, based on the consultant task table. If a consultant has a task from 31. january - 7. april, then i want the measure to count 1 for jan, feb, march, april and 0 for all other months - unless the consultant has other tasks, then i want tasks in those months to count for 1 also. Even if a consultant has 3 tasks in the same month, i want the measure to count only 1.

This measure i will use to show how many consultants we have and have had in each month historically. The end graph should look like this:

https://imgur.com/dEm6eHN

 

I hope you can help.

 

Best regards

 

Henrik

Need Help

6 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    Link calender to  Consultant Job based on Period (eg 202001)

    Then make your barchart with period on x axis and value a count distinct of consultant number from the consultant job table.

    • HKV's avatar
      HKV
      Frequent Visitor
      5 years ago

      Hi, thanks for your answer

       

      I dont think i understand how this would work, as i am not only interested in linking the start/end date to calendar, but i want to show all the months between a tasks start and end date

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    You create a slicer On Calender_date where you can select from and to

     

    You data model looks like this:

     

    Calender                        Consultant Job                 Consultant

    CalenderDate                Consultant number          Consultant number 

    Period (YYYYMM)          Period                              name

    other fields                    Consultant Job                other fields

                                          other fields

     

     

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    I forgot to say. Create a row in your consultant job table for every period

    • HKV's avatar
      HKV
      Frequent Visitor
      5 years ago

      I cant create rows - data comes from Analysis Services database and wouldnt this be an inpropriate way to solve the issue to adding rows?

      Im sorry but i cant follow your thoughts.

      Its hard for me to see how this can be solved without a measure because every period will return values for many months/dates

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    Hi HKV ,

    Which column is used to establish the relationship between Consultant and Consultant Job tables? You can create a measure as follows to obtain the number of consultants in each time period:

    Measure =
CALCULATE (
    DISTINCTCOUNT ( 'Consultant Job'[Consultant ID] ),
    FILTER (
        'Consultant Job',
        'Consultant Job'[Start Date] <= MAX ( 'Calendar'[Calendar Date] )
            && 'Consultant Job'[End Date] >= MAX ( 'Calendar'[Calendar Date] )
    )
)

    Then create a column chart: Axis: field Calendar Date of table Calendar  Values: [Measure]

    Best Regards