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.
AlB
Community Champion
Community ChampionHi 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.