Forum Discussion

tomneo007's avatar
tomneo007
Frequent Visitor
9 years ago

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

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 PRO...
Need Help
Tutorial Requests
LaurentCouartou's avatar
LaurentCouartou
Icon for Solution Supplier rankSolution Supplier
9 years ago

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.)