PowerPivot DAX: Identify running cases over time per items/cases

[tomneo007]

Hello,

I'm new in DAX

I'm working on a IT Service Desk and I would like to have a dashboard that show me the "Running Cases" over the time.

The cases have differents status like: AWAITING - IN PROGRESS - PLANNED - CLOSED.

Cases are considering as "Running Cases" when they get status above except "CLOSED"

The aim is to have the last status for each running cases (that are not closed) in order to show what is the workload

Here is the desired result:

How to arrive to this result and what are the full data ? Here is the transaction table over 4 days and for 9 cases

I tried with these and others DAX syntaxes but I didn't get the expected result.

=COUNTROWS(FILTER(Table1;MAX(Table1[Date])))

=CALCULATE(COUNTROWS(Table1);FILTER(ALL(Table1);Table1[Date]<=MAX(Table1[Date])))

The measure should be able to count all running cases that are less or equal to the current date of the evaluation context

I'm having difficulties to formulate the correct DAX syntax in order to have the desired result as above.

I would greatly appreciate your help on this matter.

Tom

[LaurentCouartou]

Using a mock up of your data, I named 'Events' the table listing the events and added a new table called 'Date' (you guess what the relationship is). This last table is recommended (especially for this kind of exercise where you might want to know the number of cases still running at a date where no event took place).

Nombre of running cases = 
	VAR DateRange = FILTER( ALL(Dates[Date]); Dates[Date] <= MAX( Dates[Date] ) )                 
	VAR Closed = CALCULATETABLE( VALUES(Events[ID_Case])
				; Events[Status] = "CLOSED"
				; DateRange
		)
	VAR Opened = CALCULATETABLE( VALUES(Events[ID_Case])
				; Events[Status] <> "CLOSED"
				; DateRange
		)
RETURN COUNTROWS(EXCEPT(Opened;Closed))

Tested on Power BI desktop, the expression uses variables for clarity. If you use Power Pivot in Excel 2013, these will not work.

Also note that, by design, the expression will continue running even past the date where you have no more data. (This can be easily fixed.)

[tomneo007]

Hi Bhavesh,

Thanks for your quick answer. I tried your measure but unfortunately it's not the desired result.

If you see the transaction table after 4 days of activities (in date of 8th of December), the result should be 3.

For all the period we still have 3 running cases. The others cases are all closed (not running)

The measure have to consider (count) all the running cases that are less or equal to the current date of the evaluation contect.

The final aim for the team of Service Desk is to have an overview of all cases are still running over all the period --> What is left in the queue

This is the challenge for this measure :-). I tried different ways but I didn't get the desired result.

Tom

[BhaveshPatel]

Sorry it was my fault.I posted the formula without reading the full question.