Line chart counting dates

[ER_newatpowerbi]

Hi,

New to this so apologize in advance if I can't articulate this correctly.

I have been trying to create a chart with a line counting items each month. Here's an image below:

 
I've been unable to create three lines for Recruit Start, Announcement Open, and Cert Received showing their outputs each month. I am able to get a chart with three lines but it's dependent on the Recruit Start. Instead, I am trying to count them separately. 

I've tried different ways through different posts here but I can't get it right. 

Hope this makes sense. Thanks in advance for your help. 

[Anonymous]

Hi @ER_newatpowerbi ,

It is glad that the solution DatawithDinesh offered help you slove it, you can accepr the way  DatawithDinesh offered as a solution so that more user can refer to it.

Best Regards!

Yolo Zhu

[ER_newatpowerbi]

It worked like a charm!   And I learned a few things today.  Thanks for your help!

[DatawithDinesh · Accepted solution]

Hello ER,

Here is the solution on what I understood from your example.

Create a date table if you already have it well and good else just create a date table here is the code.

DateTable = 
ADDCOLUMNS(
    CALENDAR(
        DATE(2024, 1, 1),    // Start Date: 1st January 2024
        TODAY()              // End Date: Today's Date
    ),
    "Year", YEAR([Date]),
    "Month", MONTH([Date]),
    "Day", DAY([Date]),
    "Month Name", FORMAT([Date], "MMMM"),
    "Year Month", FORMAT([Date], "YYYY-MM"),
    "Quarter", QUARTER([Date]),
    "Weekday", WEEKDAY([Date]),
    "Weekday Name", FORMAT([Date], "dddd"),
    "IsWeekend", IF(WEEKDAY([Date], 2) > 5, TRUE, FALSE),
    "Week Number", WEEKNUM([Date])
)

Once you have the date table create one to many relationship from date table to the data table. You will create 3 relationship 1 will be active and the other two will be inactive. I have created an active relation between date and recruit start and rest are inactive.

Now once you have the relation you just need to create a count measure using the userelationship function. Below are the three measures:

Recruitstart = COUNT(Data[Recruit Start])

CertRecieved = CALCULATE(COUNT(Data[CERT RECEIVED]),USERELATIONSHIP(Data[CERT RECEIVED],DateTable[Date]))

AnnouncementOpen = CALCULATE(COUNT(Data[ANNOUNCEMENT OPEN]),USERELATIONSHIP(Data[ANNOUNCEMENT OPEN],DateTable[Date]))

Now just put then in matrix where rows will be the month-year field from date table and then in values put these measures. Below is the output that comes up. Hope this is what you are also trying to achieve.