Month data not displaying properly in line chart

[Anonymous]

I am trying to compare activity in hours on a platform across 2020 with 2019 activity, by month. I have two separate tables, one for 2019 figures, and one for 2020, which are aggregated by month. They look like this:

Start Date         End Date           Hours of activity

01/01/2019       31/01/2019       250.2

01/02/2019       28/02/2019       315.4

...and so on. I'm trying to do a simple line chart, using Start Date at the Month hierarchy level. However, the 2019 data is showing Jan, Feb and March only with all other months grouped under Blank. I can't figure out why. No filters on the chart. 

https://www.flickr.com/photos/192824306@N06/51128104110/ Pic of chart

Bonus points for being able to do a "last 12 months" (i.e. 1st April 2020 to 31st March 2021) compared to the 12 months previous comparison.

How can I do this previous year comparison most effectively? It seems like it should be simple but I can't quite get it to work.

[Anonymous]

Thanks, I have now been able to do exactly what I wanted to do by appending the tables with the right column names and calculating a PY Hours variable using DATEADD. 

[webportal]

In order to append tables in Power Query correctly, all the columns from all the tables must contain the same names and the same data types.

Otherwise, you'll experience blank values as per your screenshot.

[Anonymous]

Thanks for the suggestion. I tried it, but I'm now more perplexed. When I appended the 2020 data to the 2021, the "hours of activity" data disappeared from Apr 2020 on downwards, although it is there in the separate tables. See null fields here.  They are not nulls in the separate table - I went back and checked.

[webportal · Accepted solution]

To get a last 12 months, you should have a single table for both years. Append table2019 to table2020 using Power Query.

Then, relate that table to a dimensional Calendar table. You may use DAX to do that table using eg: CALENDARAUTO()

To display the last 12 months values, use a matrix visual with the dates in the rows and the folloing calculated measure in values:

Last12 months = CALCULATE(SUM(Hours), DATESINPERIOD('Calendar'[Date], MAX('Calendar'[Date], -12, MONTH)