Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Reply
Anonymous
Not applicable

Oddity on Values for Report Using Date Table

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:

 

Screenshot 2020-08-23 at 16.38.12.png

 

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. 

 

Screenshot 2020-08-23 at 16.39.24.png

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:

 

Screenshot 2020-08-23 at 16.41.54.png

 

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:

 

Screenshot 2020-08-23 at 16.46.11.png

 

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:

 

Screenshot 2020-08-23 at 16.49.01.png

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).

 

InitialsLocationDate/Time
AHLW2020-01-01T21:13
AHLW2020-05-27T02:46
AHLW2020-06-21T15:29
AHLW2020-08-01T03:43
AHLW2020-08-24T02:20
AAWC2020-04-30T18:17
AAWC2020-05-09T21:43
BRMC2020-01-01T21:18
BRMC2020-02-27T21:55
BRMC2020-03-13T14:04
BM WC2020-06-16T18:28
BM WC2020-06-20T00:00
BM WC2020-06-24T06:54
BM WC2020-06-27T15:10
BM WC2020-06-27T15:30
CPMC2020-03-27T07:26
CPMC2020-06-13T03:38
CPMC2020-06-18T12:21
CPMC2020-07-19T03:00
CPMC2020-07-28T09:55
CPMC2020-07-28T11:27
CPMC2020-08-09T03:51
CWWC2020-01-08T14:23
CWWC2020-01-30T14:54
CWWC2020-03-11T11:35
CWWC2020-05-09T12:00
CWWC2020-05-21T08:22
CWWC2020-08-21T12:14
DCMC2020-07-28T11:25
DCMC2020-08-21T02:02
DH WC2020-03-03T21:08
ESKMC2020-03-03T22:10
1 ACCEPTED SOLUTION
Anonymous
Not applicable

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:

 

Screenshot 2020-08-24 at 16.19.14.png

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

 

Screenshot 2020-08-24 at 16.19.21.png

 

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. 

 

Screenshot 2020-08-24 at 16.19.38.png

 

 

View solution in original post

3 REPLIES 3
Greg_Deckler
Super User
Super User

@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.


Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
The Definitive Guide to Power Query (M)

DAX is easy, CALCULATE makes DAX hard...
Anonymous
Not applicable

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
Not applicable

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:

 

Screenshot 2020-08-24 at 16.19.14.png

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

 

Screenshot 2020-08-24 at 16.19.21.png

 

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. 

 

Screenshot 2020-08-24 at 16.19.38.png

 

 

Helpful resources

Announcements
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

MayPowerBICarousel

Power BI Monthly Update - May 2024

Check out the May 2024 Power BI update to learn about new features.

LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.