Month-End Work in Progress (WIP)

[stepoutblu]

I'm working on a set of monthly metrics that include work in progress (WIP) at the end of each month. The results will be shown on a dashboard with a slicer for the months. My datasource does not capture history or do daily snapshots, so I have to calculate WIP, which I have defined as count of tickets received prior to the end of the month that are EITHER in a status not equal to "Completed" OR have a Completed Date greater than then end of the month.  The problem I'm running into is that the slicer is filtering out anything received prior to the month selected.  For example, I have tickets received in Oct and Nov that are still open, but are being filtered out when I select April on my slicer. This is the approach I've been taking:

WIP = COUNTROWS( FILTER( 'DataTable', AND ('DataTable'[Received Date] <= LASTDATE('Calendar Table'[Date], OR ('DataTable'[Completed Date] > LASTDATE('Calendar Table'[Date], 'DataTable'[Status] <> "Completed")))

How do I get the measure to include ALL tickets received prior to the end of the selected month, regardless of the month they were received, as long as they meet one of the other two criteria?

[AlB]

Hi @stepoutblu 

Show some sample data and the expected result based on that data.

Please accept the solution when done and consider giving a thumbs up if posts are helpful.  Contact me privately for support with any larger-scale BI needs, tutoring, etc.

[stepoutblu]

Ticket Number	Status	Received Date	Completed Date
CR-0013143	In Progress	3/22/2021	null
CR-0013137	Not Started	4/16/2021	null
CR-0013144	Completed	4/22/2021	4/30/2021
CR-0000200	Completed	4/29/2021	6/4/2021
CR-0000201	Completed	4/29/2021	5/4/2021
CR-0012870	Completed	5/25/2021	6/7/2021
CR-0012934	Completed	5/31/2021	6/9/2021
CR-0012936	Completed	6/1/2021	6/8/2021
CR-0012941	Completed	6/1/2021	6/4/2021
CR-0012955	Completed	6/2/2021	6/4/2021
CR-0013001	Completed	6/4/2021	6/8/2021
CR-0013021	Completed	6/7/2021	6/9/2021
CR-0013014	Completed	6/7/2021	6/9/2021
CR-0013020	Completed	6/7/2021	6/8/2021
CR-0013047	Completed	6/9/2021	6/9/2021
CR-0013078	In Progress	6/11/2021	null
CR-0013093	On Hold	6/14/2021	null
CR-0013090	Not Started	6/14/2021	null
CR-0013039	Completed	6/15/2021	6/9/2021
CR-0013142	In Progress	6/16/2021	null

Expected results:

	Status <> Completed	Completed after end of the month	Total WIP at end of the Month
April	2	2	4
May	2	3	5
June	6	0	6

[PaulOlding · Accepted solution]

For this solution I have a disconnected Date table that will be used for the Month column of the table.

The measures all follow the same pattern.

Status <> Completed =

VAR _CurrentDate = MAX('Date'[Date])

VAR _Result =

CALCULATE(

COUNTROWS('DataTable'),

'DataTable'[Status] <> "Completed",

'DataTable'[Received Date] <= _CurrentDate

)

RETURN

_Result

Completed after month end =

VAR _CurrentDate = MAX('Date'[Date])

VAR _Result =

CALCULATE(

COUNTROWS('DataTable'),

'DataTable'[Completed Date] > _CurrentDate,

'DataTable'[Received Date] <= _CurrentDate

)

RETURN

_Result

Total WIP at end of month =

VAR _CurrentDate = MAX('Date'[Date])

VAR _Result =

CALCULATE(

COUNTROWS('DataTable'),

'DataTable'[Status] <> "Completed" || 'DataTable'[Completed Date] > _CurrentDate,

'DataTable'[Received Date] <= _CurrentDate

)

RETURN

_Result

Of course, for the last measure you could just add the results of the previous 2 measures.

You'll probably want to use month and year in your table visual otherwise the results won't make sense if you have multiple years of data.