Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago
Solved

Running Sum in Power BI for Ticket Backlog - Advanced

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...

 

MonthCreated CountResolved CountBacklogCalculation
   100<- Backlog
Jan-195040110<- 100+50-40
Feb-1910080130<- 110+100-80
Mar-19200100230 

 

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/highlight/false#M350919

 

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?

 

Need Help
Show and Tell
Tips and Tricks
Tutorial Requests
  • Anonymous's avatar
    Anonymous
    7 years ago

    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

1 Reply

  • Anonymous's avatar
    Anonymous
    Not applicable
    7 years ago

    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