Sorting dates

[DWFincher]

Hi there. I have the following fields in a table:

id, created_date, finished_date

I want to have a clustered bar chart that shows the following

January 2019 count of created_date

January 2019 count of finished_date

February 2019 count of created_date

February 2019 count of finished_date

... I am skipping the rest of the months in the year, but my chart will show all the months ...

January 2020 count of created_date

January 2020 count of finished_date

And so on and so forth...

The problem I have is, I seem to only be able to aggregate based on one date column. In sql, I would simply do a join of two queries where one aggregated based on year/month of created_date and the other based on year/month of finished_date. The solution has eluded me for a few hours now. Any tips?

Edit: I should mention that I'm not necessarily looking for all the steps. Even just a high level hint may get me where I need to go. I've tried splitting the date columns into month_number, day, year. I've tried duplicating the table and grouping. Without any of those splitting and duplicating, I can make two visualizations, one for each date field. And that does work. I just can get them to be combined into one.

2nd Edit:

As I look some more, it seems that a date table will not work as the documentation specifies these three requirements, each of which I cannot meet with my data.

• The date column must contain unique values.
• The date column must not contain BLANKs.
• The date column must not have any missing dates.

Not all rows have data which have finished. Hence, they are null.

Some rows may have the exact same date if more than one job was created on a given day.

Not every day has a job which was created.

[DWFincher]

It took a long time for me to figure out how to apply that USERELATIONSHIP function because my visualization had an implicit count on the [Date Completed] field. Since I didn't have a measure to calculate that with, I didn't have what was needed in order to use USERELATIONSHIP.  Here is what I ended up using to define the measure:

TotalCompleted = Calculate(SUM('MyRecords'[IsCompleted]), USERELATIONSHIP('MyRecordsRecords'[DateCompleted],'Date'[Date]))

The table on the left is the MyRecords table.

Thanks for pointing me in the right direction Pat!

[mahoneypat · Accepted solution]

Please see the article below. The best way is to have a date table with two relationships to your table (one inactive), and use USERELATIONSHIP in one of your measures.

Using USERELATIONSHIP in DAX - SQLBI

Pat

[DWFincher]

Thanks Pat!

I thought I would be able to dig right in to Power Bi like I did with Power Apps and Power Automate. But I'm quite lost with Power BI. It looks to me that you have given me what I need. I'm going to have to go through a lot more of the learning paths before I think I'll be able to put what you've given me to use.

I'll come back and accept your solution once I've learned how use this tool.