How to Combine 2 lines (in Line Chart) form 2 different tables

[Anonymous]

Hi,

I am new to Power BI so bear with me 🙂

I want to compare the number of application submitted this year (2022) vs last year (2021).

I am using 2 separate tables, one for 2021 and one for 2022.

I need to display 2 lines (in a line chart) where:

- one line that shows the number of application submitted from april 1st to end of June 2021, and
- a second line that shows the same thing from april 1st to end of June 2022

Basically, I want to see if are receiving more/less submission (of applications) over the same time period. Are we getting more applications for the first week of april this year compare to last year? for example.

My data looks like this:

Submission         Date Application ID 2022
05-Apr-2022       136485
01-Apr-2022       136453
04-Apr-2022       136480
03-Apr-2022       136469
01-Apr-2022       136458

and

Submission        Date Application ID 2021
07-Apr-2021      135175
07-Apr-2021      135182
05-Apr-2021       135128
07-Apr-2021       135170
06-Apr-2021       135140

When I add both Submission ID count in the value field, I get one line that is displayed correctly, the other is just a straight line (see screenshot)

I filtered the dates to just show the first 5 days.
I also try to create a relationship between both "application ID" columns,  but it doesn't work, only one line shows up.

Any help will be greatly appreciated it!

[v-janeyg-msft · Accepted solution]

Hi, @Anonymous 

Are these two tables related? Which table's date field are you placing on the visual timeline?

How to create a date table:

Table=CALENDARAUTO()

Or

Table=CALENDAR(MIN('Table'[Date]),MAX('Table 2'[Date]))

Then create the relationships, use the date of the date table on the visual.

Best Regards,
Community Support Team _ Janey
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[davehus]

Hi @Anonymous , Welcome to the world of dax and powerbi 🙂 . Have you got a date table in your model?

[Anonymous]

Hi, Thank you.

No I don't my dates are set in the "submission" columns in both tables. You mean a separate date table? How do I do that?