How to count selectively

[jackyot]

HI Experts

Newbie seeking help.......... 😅

I am looking to build a dashboard with use-case based on a legacy ticket management system. The table is one big flat file of data.

Basically the table an 'open date' and 'closed date' which will be the centre of this use-case.

On my dashboard I need to display 'Open Backlog' - The number of ticket that is still not closed given a specific date. If a period is given (as I uses a timeline slicer), the 'Open Backlog' needs to be based on the last day of the period specified.

Any hints would be very appreciated! I'm pulling my hair to get this idea around but i'm stuck and unable to go further than this.

[amitchandak]

@jackyot , refer to my blog on a similar topic if that can help

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

there are few changes to attaching the latest file after signature

[jackyot]

Hi Amit

Thanks for the reply! I had a look at it and I think the most relevant here for me would be the fomula

Last Period Employee = 
var _min_date = minx(all('Date'),'Date'[Date])
var _Expression=if(ISFILTERED('Date'[Month Year]),maxx('Date',DATEADD('Date'[Date],-1,MONTH)),maxx('Date',DATEADD('Date'[Date],-1,YEAR)))
Return
 CALCULATE(COUNTx(FILTER(Employee,Employee[Start Date]<=_Expression && Employee[Start Date]>=_min_date && (ISBLANK(Employee[End Date]) || Employee[End Date]>_Expression)),(Employee[Employee Id ])),CROSSFILTER(Employee[Start Date],'Date'[Date],None))

Can you give me a bit more explanation on the logic behind this calculation? I imagine this is a Measure placed on Employee table?