Create measure to calculate tasks based on timetable

[HKV]

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

[Anonymous]

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

[Anonymous]

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

[HKV]

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]

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]

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]

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