PowerBI Calculate and show in report the total number of occurrences for individual entries

[NickCode]

Hello everyone,

First of all thank you for your support and consideration.

I am fairly new to PowerBI and I need to create a report that would show relevant information about the data.

The data which is coming in PowerBI is structured like this and also pasted some sample data:

Container  |  IssueName  |  IssueStatus  |  TimeCreated

Cont1        |  IssueX         |  OPEN           |   10/13/2021

Cont1        |  IssueY         |  OPEN           |   10/15/2021

Cont1        |  IssueA         |  CLOSED       |   10/15/2021

Cont2        |  IssueZ         |  OPEN           |   10/15/2021

Cont2        |  IssueB         |  CLOSED       |   10/17/2021

Cont3        |  IssueY         |  OPEN           |   10/15/2021

Cont3        |  IssueY         |  OPEN           |   10/15/2021

What I would like to do is to report for each container how many OPEN or CLOSED issues are present.

Can anyone please help with a suggestion how to accomplish this ?

Thank you,

Best regards,

Nick.

[amitchandak · Accepted solution]

@NickCode , with an independent date table to select date or month

Opened = calculate(distinctCOUNT('Table'[Container]), 'Table'[IssueStatus]="Open")
Closed calculate(distinctCOUNT('Table'[Container]), 'Table'[IssueStatus]="Closed")

Open not closed on a selected date =
var _max = maxx(allselected('Date1'), 'Date1'[Date])
return
calculate([Opened] - [Closed], filter('Table', 'Table'[TimeCreated] <= _max))

Month Opened =
var _max = maxx(allselected('Date1'), 'Date1'[Date])
var _min = maxx(allselected('Date1'), 'Date1'[Date])
return
calculate(distinctCOUNT('Table'[Container]), filter('table', 'Table'[Date] >=_min && 'Table'[Date] <=_max && 'Table'[IssueStatus]="Open"))

month closed =
var _max = maxx(allselected('Date1'), 'Date1'[Date])
var _min = maxx(allselected('Date1'), 'Date1'[Date])
return
calculate(distinctCOUNT('Table'[Container]), filter('table', 'Table'[Date] >=_min && 'Table'[Date] <=_max && 'Table'[IssueStatus]="Closed"))