Forum Discussion
Calculate Count Of Rows Per Month
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.
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/
2 Replies
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/
That has worked Excellently, thank you.
