Calculate Count Of Rows Per Month

[LewisH]

We Have a Table ' Service Desk List' Whcih has two fields, "Requested Date" and "Resolved Date". How can I calculate how many of each are populated within each month.  I am aiming to use this in a Line Graph to show the difference. 

[vanessafvg · Accepted solution]

@LewisH

are you wanting to plot different counts of resolved vs requests?

1.firstly you need a date table do you have one?

2.then you need to create a relationship between both of your  dates and the date table .  when you create these relationships one will be an active relationship (default), the other will be an inactive relationship.

3.  create a count measure for each of the date relationships.

For whichever is the active relationship assuming its the requested date

requests = countrows(table)

resolved = calculate(countrows(table), userelationship(table[resolveddate], datetable[date])

then you can drag your date onto the graph and your 2 measures.

might sound confusing if you haven't done this before

here are some links

https://www.mattmasson.com/2014/02/creating-a-date-dimension-with-a-power-query-script/

https://powerbi.microsoft.com/en-us/documentation/powerbi-desktop-create-and-manage-relationships/

[LewisH]

That has worked Excellently, thank you.