Bar Graph Troubles, incorrect values

[johanhallin]

Hi,

I have search the forum but not found anything around my problem so I am hoping for some help.

I have a table with many columns where two are of interest, CreatedDate and MovedDate. Both contains a simple date and I would like to have a "Created vs Closed" kind of graf but it does not add up. If I look at the raw data for a specifik date for each column, I can see the count beeing incorrect in the graph (bar graph at the moment).

My problem is most likely that I don't understand what field/value to put for the axis. I might need a new table/comlumn that combines the date from the both columns to get a correct result. But If I only use the CreatedDate as the axis it still counts incorrect for the MovedDate for a specific day.

ExampleData:

CreatedDate

2018-03-13

2018-03-09

2018-03-13

2018-03-08

2018-03-13

2018-03-08

2018-03-09

MovedDate

2018-03-13

2018-03-09

2018-03-13

2018-03-13

2018-03-13

2018-03-11

2018-03-09

Which gives me graph where MovedDate bars are never higher than CreateDate bar which is incorrect. And no values for 2018-03-11, which would be nice!

Does anyone have any ideas for what I am missing? 
I might be able to extract the data into excel if that would help!

Kind regards, Johan

[jthomson]

Not sure exactly what you're trying to do, but if you're playing with multiple date fields then you really, really need a calendar table to use as your axis

[johanhallin]

That sounds like my problem...so...I should create a seperate table as a calendar? What do I fill in in that new table? A outerjoin of the two columns of some sort?

Any guidlines?

//Johan

[johanhallin]

Ok, so I managed to create a new table for the date calender using Date = CALENDAR (DATE(2016;1;1); DATE(2020;12;31)).

But I'm still not given any resonable bar graph. Not it displayes the total count for created date as a flat line for all dates....not "per date" as I want it to be...

I am surley missing something easy and basic but I can't figure out what. 

I have tried adding a relationship to my new table as well...no difference...

//johan

[jthomson]

You need to make relationships between the calendar table and your date field(s)

[johanhallin]

Yeah, I figured that too...but it did not solve my problem. Now both the CreateDate and the MovedDate shows the same count even if the raw data shows that MovedDate count should be 15 and the CreateDate count should be 5.

I can't have two active relationships, but I have one active and one inactive relationship between these two tables now. The bar graph displays the values for CreatedDate and MovedDate as the same value, altogh different for each day...

//Johan

[jthomson]

I'm not sure what you're shoving into your graph but try making a measure to count instances, using the relationship in question for whichever is currently inactive. It sounds like it's using the active relationship for both counts.

[johanhallin]

When I change the active relationship i get different graphs so I think you are on to something. How can I get it to not use the active relationship for both counts?

I tried adding measurements but it does not seems to make a different...or I did it in the wrong table...or something...

[johanhallin]

As seen in this screenshot, when I plot the values in different graphs, then look fine. But when I combine them, Power Bi seems to be using the active raltionship to display the data incorrectly. The left-most column shows 12 Moved in the middle graph and 9 Created in the third graph but in the first combined graph only 6 moved issues are shown. 

Any idea how this can be prevented?

[johanhallin]

Anyone?

[Anonymous]

Did you solve this problem? Cause I have a similar problem counting the amount of dates that has occured in a month where the graphs do not show the correct amount..

[johanhallin]

Hi,

i did not solve this problem and moved on to another solution (and a different tool).

One really ugly way I used for a short while was to create two graphs with the same date range and positioned them over eachother with transparant background using line graphs. It worked suprisingly well but was not a good solution.

At the moment I am thinking about implementing a seperate databse and mining the data from our issue handling system (Jira from Atlassian) to be able to display the trends the way I need.

Kind regards, Johan