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 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:
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.
3 Replies
- AlB
Community Champion
Hi Anonymous
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.
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 
PaulOldingSolution Sage
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.
