Open Ticket Count

[Anonymous]

Hi Everyone,

I have a below requirement and I am struggling to get this done and looking for some help. If anyone has any suggestions on how to acheive this, please let me know and that would be really helpful and appreciated. 

My requirement is we need to show all the count of OPEN cases based on when the case was opened and when it was closed. Example if the case is opened on 01/01/2021 and closed on 05/01/2021, in this case we need to count this case as '1' open case count in all the months of JAN, FEB, MAR and APRIL and we should not count for MAY as the case was closed in the month of MAY.

Note: In-my case my source of data is raw file (excel) and I am importing this excel raw file into Power BI and from there working on this requirement. Below is the output screenshot that I have to acheive, currently I have did everything manually in spreadsheet to show my scenario. Columns Jan 21 to Mar 22 are not on my data set, I have only Ticket number, State, Actual Start and Actual End column, I do have calendar table.

.

[Anonymous]

Hi @Anonymous ,

I create a sample by your data. My calendar table is as below.

DimDate =
ADDCOLUMNS (
    CALENDAR ( DATE ( 2021, 01, 01 ), DATE ( 2022, 03, 31 ) ),
    "MonthYear",
        YEAR ( [Date] ) * 100
            + MONTH ( [Date] ),
    "MonthYearName", FORMAT ( [Date], "MMM-YY" )
)

Please try my codes to create measures.

Basic Flag = 
VAR _Actual_start = MAX('Table'[Actual start])
VAR _Actual_end = MAX('Table'[Actual end])
VAR _Month_end = MAX(DimDate[Date])
RETURN
IF(_Actual_start<=_Month_end,IF(_Actual_end = BLANK() || _Actual_end>=_Month_end,1,BLANK()))

Sum = 
SUMX(VALUES('Table'[ID]),[Basic Flag])

Result is as below.

Best Regards,
Rico Zhou

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

Hi Rico,

Thank you for the help, for some reason still i am not able to get the expected result set, here is the expected result set but i am getting 12 for the month of Feb 2021

this is the expected resultFiltered on End date (tickets opened and closed on Jan and Feb)Filter on Actual startfor feb it is showing 12 it should be 23

[ryan_mayu]

@Anonymous 

pls see the attachment below

[Anonymous]

Hi Ryan,

Thank you for your help, it is working fine for Jan month but from Feb it is not giving accurate numbers, it is giving what ever the open count left from JAN which are closed in feb for example if the Jan have 10 tickets and 2 got closed in Jan itself then Jan count is 8 this is working fine but from that 8 tickets if 2 were closed in Feb it is giving remaining 6 count for Feb it is not adding Feb opened ticket count.

Thanks.

[ryan_mayu]

@Anonymous 

i updated the sample data.

like what you said, 10 tickets in Jan and 2 closed in Jan, then the result for jan is 8.

2 tickets colsed in Feb and one open in Feb, the the result for Feb is 8-2+1=7