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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

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
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors