monthly kpi when only start and end date available

[iou812]

Hello All -

If I have a list of data that looks like this...

issue1 opendate1 closeddate1

issue2 opendate2 closeddate2

issue3 opendate3 closeddate3

...

[And some entries where there is no closeddate, because the issue is still open...]

What is the easiest/best way for me to produce a chart that shows the number of open issues per month?  For example,

NumberOfOpen   x      y      z   ...

Month                 Jan  Feb  Mar ...

Any/all insights greatly appreciated.  Thanks!

Tom

[AlexChen]

Hi,

I assume you have a table like below.

Firstly Add  a new column called "Month" from "openDate" which shows the month of "openDate" in Query Editor.

In this table, If you want to calculate the issues that have blank closeDate for everymonth, you can use measure below.

NumOfIssues = CALCULATE(COUNTAX(issueTable, issueTable[issue]), Filter(issueTable, issueTable[closeDate] = BLANK()))

But If you also want to calculate the issues whose closeDate is after the current month of openDate, for example, issue2,

It is closed in February, not in January, you can use measure below:

NumOfIssues = CALCULATE(COUNTAX(issueTable, issueTable[issue]), Filter(issueTable, issueTable[closeDate] = BLANK() || And(issueTable[closeDate] <> BLANK(), FORMAT(issueTable[closeDate], "yyyymm") > FORMAT(issueTable[openDate], "yyyymm"))))

Best Regards

Alex

[MattAllington · Accepted solution]

Take a look at this https://community.powerbi.com/t5/Desktop/Open-Tickets-per-group-by-date/m-p/9059#M1661