Aggregate total number per month (on line graph)

[JustinDoh1]

I am sharing my Pbix file here.

I am trying to create a line graph that shows lump sum for each month's data (instead of showing individual dates in a month).

Expected output is something like this:

There is a solution provided earlier on this link.

Bottom is the solution:

But, I am not sure how I should apply.

First, do I have to create a new calendar table (like bottom) and link (create a relationship) to my date column ("IncidentDate")?

If I try to create a date hierarchy from existing date field ("IncidentDate"), it would not stretch to Year, Quarter, Month and Day.

It just stops at "IncidentDate".

So, this is where I am stuck.

Should I create a relationship with newly created calendar table or what do I do if my existing date field does not create hierarchy?

Thanks.

[ryan_mayu]

@JustinDoh1 

pls see the attachment below

[JustinDoh1 · Accepted solution]

@ryan_mayu Thank you Ryan. I now understand the way to lump sum for month (using measure).

[HotChilli]

There are a few things in the provided model which don't look right.  

Put the Date from the 'Classic Date' table on the x-axis (instead of IncidentDate from the fact table).  That's why you created the date table.

Also  the relationship is not working correctly because IncidentDate is a datetime and the Date in 'Classic Date' is a date.  So create another column in vw_inc_incident which is a date version of IncidentDate and use that in the relationship.

Also the measure in the Values field of the visualisation is a sum of IncidentNumber.  Is this what you want to do?  Is IncidentNumber a financial amount or is it an Id of some sort?  If it is an Id you don't want to sum it.  So have a think about that too.

Good luck

[JustinDoh1]

@HotChilli Thank you for your help. I got a better understanding how to create relationship with same date type.