Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
avanti295
Regular Visitor

History Capture

Hello Friends,

I have open and closed incidents and I need history of an open incidents and closed incidents for example

Jan month I have 10 open incidents and 2 got closed in Jan and 5 got closed in Feb now on the chart for Jan month it should show 8 open count and 2 closed count and when we slide into Feb month, we have to consider 8 open counts from Jan and if there are 10 cases opened in Feb month then the count would need to be 8 (jan Open) + 10 (new Feb Open) = 18 (cases should be for Feb), this is how I am looking for, if anyone knows on how we can get this acheived in Power BI, please do let me know your thoughts and thanks for all your help in advance!!

 

Below is the sample data

 

Incident

Start date

End date

Days aging

  

INC001

1/1/2023

1/3/2023

2

  

INC002

1/2/2023

1/10/2023

8

  

INC003

1/3/2023

2/10/2023

38

  

INC004

1/4/2023

2/8/2023

35

  

INC005

1/5/2023

2/20/2023

46

  

INC006

1/6/2023

2/25/2023

50

  

INC007

1/7/2023

3/2/2023

54

  

INC008

1/8/2023

4/3/2023

85

  

INC009

1/9/2023

5/1/2023

112

  

INC010

2/1/2023

2/3/2023

2

  

INC011

2/2/2023

3/2/2023

28

  

INC012

2/3/2023

3/5/2023

30

  

INC013

2/4/2023

4/2/2023

57

  

INC014

3/1/2023

3/5/2023

4

  

INC015

3/2/2023

3/20/2023

18

  

INC016

3/3/2023

4/15/2023

43

  

INC017

3/4/2023

4/20/2023

47

  

INC018

4/1/2023

4/3/2023

2

  

INC019

4/2/2023

5/2/2023

30

  

INC020

5/1/2023

5/3/2023

2

  

Inc021

5/4/2023

    

 

My expected result would be like shown below.

 

 open count Closed count Avg Open duration Avg Closed duration
Jan-2372485
Feb-23654934
Mar-2355  
Apr-2325  
May-2313  
1 ACCEPTED SOLUTION
v-rzhou-msft
Community Support
Community Support

Hi @avanti295 ,

 

I suggest you to create an unrelated calendar table to help your calculation.

Calendar = 
ADDCOLUMNS (
    CALENDAR (
        EOMONTH ( MIN ( 'Table'[Start date] ), -1 ) + 1,
        EOMONTH ( MAX ( 'Table'[End date] ), 0 )
    ),
    "Year", YEAR ( [Date] ),
    "Month", FORMAT ( [Date], "MMMM" ),
    "MonthSort", MONTH ( [Date] ),
    "MM-YY", FORMAT ( [Date], "MMM-YY" ),
    "YearMonth",
        YEAR ( [Date] ) * 100
            + MONTH ( [Date] )
)

Measure:

Open count = 
CALCULATE (
    COUNTROWS ( 'Table' ),
    FILTER (
        'Table',
        AND (
            'Table'[Start date] <= MAX ( 'Calendar'[Date] ),
            OR (
                'Table'[End date] > MAX ( 'Calendar'[Date] ),
                'Table'[End date] = BLANK ()
            )
        )
    )
)
Closed count = CALCULATE(COUNTROWS('Table'),FILTER('Table','Table'[End date] in VALUES('Calendar'[Date])))
Avg Open duration = 
CALCULATE (
    AVERAGE ( 'Table'[Days aging] ),
    FILTER (
        'Table',
        AND (
            'Table'[Start date] <= MAX ( 'Calendar'[Date] ),
            OR (
                'Table'[End date] > MAX ( 'Calendar'[Date] ),
                'Table'[End date] = BLANK ()
            )
        )
    )
)
Avg Closed duration = CALCULATE(AVERAGE('Table'[Days aging]),FILTER('Table','Table'[End date] in VALUES('Calendar'[Date])))

 Result is as below.

vrzhoumsft_0-1685341181924.png

You can download the attatch file to learn more details.

 

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.

View solution in original post

1 REPLY 1
v-rzhou-msft
Community Support
Community Support

Hi @avanti295 ,

 

I suggest you to create an unrelated calendar table to help your calculation.

Calendar = 
ADDCOLUMNS (
    CALENDAR (
        EOMONTH ( MIN ( 'Table'[Start date] ), -1 ) + 1,
        EOMONTH ( MAX ( 'Table'[End date] ), 0 )
    ),
    "Year", YEAR ( [Date] ),
    "Month", FORMAT ( [Date], "MMMM" ),
    "MonthSort", MONTH ( [Date] ),
    "MM-YY", FORMAT ( [Date], "MMM-YY" ),
    "YearMonth",
        YEAR ( [Date] ) * 100
            + MONTH ( [Date] )
)

Measure:

Open count = 
CALCULATE (
    COUNTROWS ( 'Table' ),
    FILTER (
        'Table',
        AND (
            'Table'[Start date] <= MAX ( 'Calendar'[Date] ),
            OR (
                'Table'[End date] > MAX ( 'Calendar'[Date] ),
                'Table'[End date] = BLANK ()
            )
        )
    )
)
Closed count = CALCULATE(COUNTROWS('Table'),FILTER('Table','Table'[End date] in VALUES('Calendar'[Date])))
Avg Open duration = 
CALCULATE (
    AVERAGE ( 'Table'[Days aging] ),
    FILTER (
        'Table',
        AND (
            'Table'[Start date] <= MAX ( 'Calendar'[Date] ),
            OR (
                'Table'[End date] > MAX ( 'Calendar'[Date] ),
                'Table'[End date] = BLANK ()
            )
        )
    )
)
Avg Closed duration = CALCULATE(AVERAGE('Table'[Days aging]),FILTER('Table','Table'[End date] in VALUES('Calendar'[Date])))

 Result is as below.

vrzhoumsft_0-1685341181924.png

You can download the attatch file to learn more details.

 

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.

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.