Counting a table of dates

[Anonymous]

Hi all,

Essentially, I have a table with three date columns: Opening Date. Commencement Date, Closing Date. Each columns has about 1000 rows of data values. Each of these columns is reading as date fields in PowerBi when I've loaded and transformed the data.

All I want to do is produce three bar charts, which has time on the x-axis (Months & Year - e.g. Feb, 2021, March 2021 etc) and a bar height (i.e. y-axis) equal to a count of the number of Opening Date, Commencement Dates and Closing Dates that fit within that period. 

However, I am struggling because, whilst I have created a separate date table, I cannot create a relationship between the date table and the table of Opening / Commencement / Closing Dates as they all need to have separate and unique relationships with the date table.

Can someone please assist me with this? 

[v-luwang-msft]

HI @Anonymous ,

What amitchandak said is right .

for a sample:

Suppose two tables, the order table and the date table, the order table with the order date and the date of shipment, need to be analyzed in accordance with the order date and the date of shipment, respectively, but between the two tables can only have a field with the date table to establish a relationship, if the order date and the date table to establish a solid line relationship, then the date of shipment and the date table to establish the relationship can only be indicated by a dashed line, called the candidate relationship.

Activate the candidate relationship using the USERELATIONSHIP function, e.g. to calculate the sales amount according to the date of dispatch.

=CALCULATE([sales],USERELATIONSHIP('date table'[date],'order table'[date of dispatch])).

When the candidate relationship is activated, sales will be counted by ship date and the existing solid line relationship (i.e. the association of the order date with the date table) will be automatically closed in this calculation.

To learn more details，you could read the following article:

USERELATIONSHIP

Wish it is helpful for you!

Best Regards

Lucien

[amitchandak · Accepted solution]

@Anonymous , You can create a date table and join these dates with that. Only one join will be active. Rest will be inactive. you can activate those using userelationship

refer this blog

https://community.powerbi.com/t5/Community-Blog/HR-Analytics-Active-Employee-Hire-and-Termination-trend/ba-p/882970