How to count tickets carrying over the month

[sandip]

Hi,

I need to calculate count tickets carrying over the month and below is the data set link:

https://drive.google.com/file/d/1zOPAPGx1ovtF4IdM80Li2WqCkjDYku7a/view?usp=sharing

and below is the data set look like:

Ticket	Date Created	Date Closed
1	1-Jan	31-Jan
2	1-Jan	1-Feb
3	15-Mar	1-Jun
4	31-May	1-Jun
5	8-Feb	1-Apr
6	1-May	15-May
7	31-Jan	1-Jun
8	15-Jun	18-Jun
9	1-Mar	1-May
10	1-Jan	2-Jan

I have done a sample PBIX file for it, but it does not calculate correctly and below is the link of the report:

https://drive.google.com/file/d/1mo4iqptANkFQ3vr4nV2ASxdNLqMjzwzV/view?usp=sharing

so the count should look likie this:

Month	Count of In Progress
Jan	4
Feb	3
March	4
April	4
May	5
June	4

I am using the below expression:

Carryover Tickets1 =

VAR __mindate = MIN ( 'Calendar'[Date] )

VAR _maxdate = MAX( 'Calendar'[Date] )

RETURN

CALCULATE (

DISTINCTCOUNT( 'Demo Data'[Ticket ] ),

'Demo Data'[Date Created] <= __mindate,

'Demo Data'[Date Closed] >= __mindate || ISBLANK('Demo Data'[Date Closed] )

)

and I am getting below result which is wrong:

Month-Year	Carryover Tickets1
20-Apr	4
20-Feb	2
20-Jan	3
20-Jun	3
20-Mar	3
20-May	4

can any one help me how to correct the expression?

[v-juanli-msft · Accepted solution]

Hi @sandip 

Create measure

Measure =
VAR min1 =
    MIN ( 'Calendar'[Date] )
VAR max1 =
    MAX ( 'Calendar'[Date] )
RETURN
    CALCULATE (
        DISTINCTCOUNT ( 'Demo Data'[Ticket ] ),
        FILTER (
            'Demo Data',
            max1 >= [Date Created]
                && ( max1 <= [Date Closed]
                || min1 <= [Date Closed] )
        )
    )

Best Regards
Maggie
Community Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[sandip]

Hi,

Thank you for your solution. It looks like you have that PBIX file with the propoer solution, can I have it or can you attach it at this forum so that I can go though it?

Thanks,

Sandip

[v-juanli-msft]

Hi @sandip 

Here.

Best Regards

Maggie

[amitchandak]

@sandip , refer if this blog can help

https://community.powerbi.com/t5/Community-Blog/HR-Analytics-Active-Employee-Hire-and-Termination-tr...

or refer if this can help

https://www.dropbox.com/s/bqbei7b8qbq5xez/leavebetweendates.pbix?dl=0

[sandip]

Hi,

I have shared a PBIX file , Can you please check what I have wrong there?

I really need this help. Aslo If an employee terminated at the same month of joining, then your below expression will not work and I have tested it:

Current Employees = CALCULATE(COUNTx(FILTER(Employee,Employee[Date Created]<=max('Date'[Date]) && (ISBLANK(Employee[Date Closed]) || Employee[Date Closed]>max('Date'[Date]))),(Employee[Ticket])),CROSSFILTER(Employee[Date Created],'Date'[Date],None))

Below is the data set for reference:

Start Date	End Date	Employee  Id
1/1/2015 0:00	1/28/2015 0:00	1
1/1/2015 0:00	1/21/2015 0:00	2
2/10/2015 0:00	12/31/2015 0:00	3

So can you please give correct expression which will consider the employee number who has resigned at the same month of joining?

Thanks,

Sandip