Running Sum in Power BI for Ticket Backlog - Advanced

[Anonymous]

I have IT Tickets data with below shown columns

1. Ticket Number
2. Ticket Status
3. Ticket CreatedDate
4. Ticket ResolvedDate

I would like to create a report like below...

Month	Created Count	Resolved Count	Backlog	Calculation
			100	<- Backlog
Jan-19	50	40	110	<- 100+50-40
Feb-19	100	80	130	<- 110+100-80
Mar-19	200	100	230

I have the raw data and PBIX in one drive for you review.

https://1drv.ms/u/s!AuZ8zsEu-lf-aKZZdq9ll8lX0JA?e=Z93Vf4

Problem is I have done this in Tableau and QlikView in just a right click on mouse. But I have no clue on how to achieve these three metrics in a single table in Power BI. I have the similar requirement to show the same table by IT Team list instead of Month list etc.,

For the above I have a perfect solution through

https://community.powerbi.com/t5/Desktop/Running-Sum-in-Power-BI-for-Ticket-Backlog/m-p/727202/highl...

Below shown is the screenshot of solution which is correct.

BUT when i filter to select only FEB MAR APR. The backlog count is showing wrongly.. What is the reason and how to correct this?

[Anonymous]

I have IT Tickets data with below shown columns

1. Ticket Number
2. Ticket Status
3. Ticket CreatedDate
4. Ticket ResolvedDate

I would like to create a report like below...

Month	Created Count	Resolved Count	Backlog	Calculation
			100	<- Backlog
Jan-19	50	40	110	<- 100+50-40
Feb-19	100	80	130	<- 110+100-80
Mar-19	200	100	230

I have the raw data and PBIX in one drive for you review.

https://1drv.ms/u/s!AuZ8zsEu-lf-aKZZdq9ll8lX0JA?e=Z93Vf4

Problem is I have done this in Tableau and QlikView in just a right click on mouse. But I have no clue on how to achieve these three metrics in a single table in Power BI. I have the similar requirement to show the same table by IT Team list instead of Month list etc.,

For the above I have a perfect solution through

https://community.powerbi.com/t5/Desktop/Running-Sum-in-Power-BI-for-Ticket-Backlog/m-p/727202/highl...

Below shown is the screenshot of solution which is correct.

BUT when i filter to select only FEB MAR APR. The backlog count is showing wrongly.. What is the reason and how to correct this?

[Anonymous · Accepted solution]

Hi @Anonymous ,

You can try to use following measure formulas if it works: 

cCreated = 
CALCULATE (
    COUNT ( data[ID] ),
    FILTER (
        ALLSELECTED ( data ),
        FORMAT ( [Created], "mm/yyyy" ) = FORMAT ( min ( 'Table'[Date] ), "mm/yyyy" )&&[Created]<>BLANK()
    )
)

cResolved = 
CALCULATE (
    COUNT ( data[ID] ),
    FILTER (
        ALLSELECTED ( data ),
        FORMAT ( [Resolved], "mm/yyyy" ) = FORMAT ( min ( 'Table'[Date] ), "mm/yyyy" )&&[Resolved]<>BLANK()
    )
)

cUnsolved = [cCreated]-[cResolved]

BackLog = 
VAR currDate =
    MIN ( 'Table'[Date] )
VAR prev =
    SUMX (
        SUMMARIZE (
            FILTER ( ALL ( data ), [Created] < currDate ),
            [Created].[Year],
            data[Created].[Month],
            "Count", COUNT ( data[ID] )
        ),
        [Count]
    )
        - SUMX (
            SUMMARIZE (
                FILTER ( ALL ( data ), [Resolved] < currDate ),
                [Resolved].[Year],
                [Resolved].[Month],
                "Count", COUNT ( data[ID] )
            ),
            [Count]
        )
RETURN
    prev + [cUnsolved]

Regards,

Xiaoxin Sheng