Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago

How to count selectively

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.

 

 

 

 

 

 

 

 

 

Need Help

2 Replies

    • Anonymous's avatar
      Anonymous
      Not applicable
      5 years ago

      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?