Forum Discussion
Month-End Work in Progress (WIP)
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 cap...
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_ResultCompleted after month end =VAR _CurrentDate = MAX('Date'[Date])VAR _Result =CALCULATE(COUNTROWS('DataTable'),'DataTable'[Completed Date] > _CurrentDate,'DataTable'[Received Date] <= _CurrentDate)RETURN_ResultTotal 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_ResultOf 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.
| 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
Solution Sage
Solution SageFor 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.