How to count the number of active contracts per month when the months are the columns

[Anonymous]

Essentially, I have data like this:

Contracts

name,      work_type_id, start_date, end_date
contract1, 1,            1.1.2023,   31.5.2023
contract2, 2,            1.2.2023,   31.8.2023
contract3, 3,            1.4.2023,   31.5.2023
contract4, 2,            1.4.2023,   31.7.2023

Work types

name,         id, count_of_contracts
fix_plumbing, 1,  1
renovation,   2,  2
painting,     3,  1

These two tables are related by contracts.work_type_id and work_types.id.

I would like to have the data in this format:

name,        work_type, count_of_contracts_in_jan, count_of_contracts_in_feb, count_of_contracts_in_mar, count_of_contracts_in_apr, count_of_contracts_in_may, count_of_contracts_in_jun, count_of_contracts_in_jul, count_of_contracts_in_aug
fix_plumbing, 1,        1, 1, 1, 1, 1, 0, 0, 0
renovation,   2,        0, 1, 1, 2, 2, 2, 2, 1
painting,     3,        0, 0, 0, 1, 1, 0, 0, 0

In this output format, each month (from jan to dec) is a column, work types are rows, and the number of active contracts for each work type are the values. There are examples where the months are rows, but that is not sufficient in my case. 

[Ahmedx · Accepted solution]

pls try this

Sample PBIX file attached
https://1drv.ms/u/s!AiUZ0Ws7G26Rh1kEh6qY-nXIMNGc?e=0r4ror

[amitchandak]

@Anonymous , Please refer to my HR current employee approach in Blog and attached file, I think that can help in this case

Power BI: HR Analytics - Employees as on Date : https://youtu.be/e6Y-l_JtCq4
https://community.powerbi.com/t5/Community-Blog/HR-Analytics-Active-Employee-Hire-and-Termination-trend/ba-p/882970

Usererlationship, inactive join, crossfilter to help