Noobie question about line graphs, months and values

[valleyman]

Hi

Still very much a noobie on PBI, but have been getting along quite nicely upto now. 

I have data that is a record of each time something happened to someone, in this case people going to hospital.

The data shows that in Dec 19, 2 people were admitted. In Jan 20, 3 people were admitted. In Feb 20, none were admitted. In March 20, 1 person was admitted on one occasion, and in April 20, so far 5 people have been admitted. This data is currently in row format, and doesn't give the totals for the months in question. 

What I'm after is the way to produce this graph. The instances through the months have been totalled and shown like this. How do I do this in PBI? I've been able to do all kinds of other graphs, gauges, pie charts and display tiles through importing data from the SQL stored procedures I've written, but I just cannot get my head round the way in which PBI does this one element. 

Any help would be gratefully received.

Thanks

Ant

[Pragati11]

Hi @valleyman ,

You can create a line chart in Power BI, with DATE on x-axis and count of Number of Admits on Y-axis.

you can refer following link for line charts in Power BI:

https://docs.microsoft.com/en-us/power-bi/visuals/power-bi-line-chart

If this helps and resolves the issue, appreciate a Kudos and mark it as a Solution! 🙂

Thanks,

Pragati

[amitchandak]

@valleyman , Not clear. Can you share sample data and sample output.

[valleyman]

 @amitchandak Ignore the counts as I made them up for the purposes of the Excel example I was making, in order to make the graph I need to see in PBI. 

[Pragati11]

Hi @valleyman ,

After seeing your screenshot with sample, you can move your DATEDONE column to the x-axis of line chart. on y-axis you can move COUNT(WORKER) or COUNT(SERVICEUSER) as required on te chart.

If this helps and resolves the issue, appreciate a Kudos and mark it as a Solution! 🙂

Thanks,

Pragati

[valleyman]

@amitchandak thank you very much. That does work and I've kinda got to this point in the end before, but with the problem that the December 2019 figure is showing ahead of April 2020...

How do I get it to look like the Excel example please? I get that it's because the DH shows Month and not Year, but I don't know how to rectify it where it doesn't show me the year info first. This is my Visualisation config below:

[amitchandak · Accepted solution]

In You date table create month -year

Month - year = format([Date],"MMMM-YYYY")

Or

Month - year = format([Date],"MMM-YYYY")

In above case you need a sort column and mark that as sort column of first

Month - year Sort = format([Date],"YYYYMM")

https://docs.microsoft.com/en-us/power-bi/desktop-sort-by-column

[Pragati11]

Hi @valleyman ,

This is Pragati.

On your chart move your complete "DATEDONE" column. Then on the line chart there is a capability to drill down on dates:

Year --> Quarter --> Month --> Day

If you just want Month-Year combination of your date column, once you move your date column to the chart, it shows the whole hierarchy as follows:

From the above just keep Year & Month. Then on the chart when you drill down completly, your date is shown as Month-Year format.

If this helps and resolves the issue, appreciate a Kudos and mark it as a Solution! 🙂

Thanks,

Pragati