Oddity on Values for Report Using Date Table

[Anonymous]

I have the following set of data, just one of many rows of a similar nature but in this instance for January 2020, showing the number of falls in a building. There are three such tables, splitting the falls into varying levels of severity:

If I create a stacked column chart on this data in PBI, I get these results, which are correct and tally with the data above. Please forgive the lack of proper formatting of the visual, I'm doing this for demo purposes only. 

 

However, following advice given on a previous post and wanting to use it in this instance, I wanted to show the type / seriousness of the various falls over the year so far on one visual, rather than 3 as I have at the moment. I believed I needed to create a date calendar table to use as a go-between or link table. So I created this below. It goes from 1/1/2020 to a few years in the future, and also added some additional date / month identifiers should I need them further down the line:

I then created a new column in my data that took the date of DateDone and created a new value ie 2020-01-01T21:13 = 01 January 2020, so it would link in with the calendar table. I created relationships from the calendar table to the various Falls tables:

I then created a new visual stacked column chart, and set it up to take Counts of the Note fields in each table to give me a number of records, and used the Date Hierarchy from the calendar table as its X axis, which should give me ie 14 in January, 16 in February, 37 in March etc. However, it does not:

Not only are the values not correct, but there are also figures in Sept - December when the data only begins in January 2020, and ends in August 2020. 

So, I'm obviously missing a trick here that's a) incorrectly totalling but presumably because b) it's not assigned the correct months. 

Any advice would be greatly appreciated, as I want to have all 3 data tables incorporated to show the total number of falls in that month, but spread across the three severities. 

Thanks

EDIT - added some of the data, though it is essentially as above (I've removed individual identifiers and also the unrequired additional info).

Initials	Location	Date/Time
AH	LW	2020-01-01T21:13
AH	LW	2020-05-27T02:46
AH	LW	2020-06-21T15:29
AH	LW	2020-08-01T03:43
AH	LW	2020-08-24T02:20
AA	WC	2020-04-30T18:17
AA	WC	2020-05-09T21:43
BR	MC	2020-01-01T21:18
BR	MC	2020-02-27T21:55
BR	MC	2020-03-13T14:04
BM	WC	2020-06-16T18:28
BM	WC	2020-06-20T00:00
BM	WC	2020-06-24T06:54
BM	WC	2020-06-27T15:10
BM	WC	2020-06-27T15:30
CP	MC	2020-03-27T07:26
CP	MC	2020-06-13T03:38
CP	MC	2020-06-18T12:21
CP	MC	2020-07-19T03:00
CP	MC	2020-07-28T09:55
CP	MC	2020-07-28T11:27
CP	MC	2020-08-09T03:51
CW	WC	2020-01-08T14:23
CW	WC	2020-01-30T14:54
CW	WC	2020-03-11T11:35
CW	WC	2020-05-09T12:00
CW	WC	2020-05-21T08:22
CW	WC	2020-08-21T12:14
DC	MC	2020-07-28T11:25
DC	MC	2020-08-21T02:02
DH	WC	2020-03-03T21:08
ESK	MC	2020-03-03T22:10

[Greg_Deckler]

@Anonymous - It's a bit difficult to follow. Sample/example data the recreates the problem would help tremendously. One question, why not append all of your fall tables together with a column indicating severity? Seems like that would simplify things quite a bit.

Please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

The most important parts are:
1. Sample data as text, use the table tool in the editing bar
2. Expected output from sample data
3. Explanation in words of how to get from 1. to 2.

[Anonymous]

I haven't put all the tables together because they aren't just a sum and there aren't any identifiers within that data that says "this is severity 1", "this is severity 2". Well, there are, but I've already performed that process and don't want to change things like that. I've added some of the data, but you will see that none of it goes between Jan 2020 and Aug 2020.

[Anonymous · Accepted solution]

I've figured it out myself, and in retrospect I should've realised with those dates later in the year. My DateDone is in UK date format, whereas for some reason it was converting the new column into US format. Odd though, as the forumula for Date was dd/mm/yyyy, and not mm/dd/yyyy:

Anyway, I created another new column called NewDateNAI (for the NAI table) but to more closely match my Calendar date table:

I then noticed, where I hadn't before (and should have...) that DateDone was ie 02/06/2020, but my first Date column based on it was showing 06 February 2020. Obviously this is wrong. Once I corrected this issue, my graph has started showing correct values in the corresponding months, and there are no longer any entries for Sept - Dec. 

Just for something to be aware of here, as I would've thought "dd/mm/yyyy" would stipulate UK date format, but it did not. I'm unsure why.