cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
stepoutblu
Frequent Visitor

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:

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?
1 ACCEPTED SOLUTION

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

PaulOlding_0-1624566152454.png

 

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.
PaulOlding_1-1624566422385.png

 

View solution in original post

3 REPLIES 3
AlB
Super User
Super User

Hi @stepoutblu 

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

SU18_powerbi_badge

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
Frequent Visitor

Ticket NumberStatusReceived DateCompleted Date
CR-0013143In Progress3/22/2021null
CR-0013137Not Started4/16/2021null
CR-0013144Completed4/22/20214/30/2021
CR-0000200Completed4/29/20216/4/2021
CR-0000201Completed4/29/20215/4/2021
CR-0012870Completed5/25/20216/7/2021
CR-0012934Completed5/31/20216/9/2021
CR-0012936Completed6/1/20216/8/2021
CR-0012941Completed6/1/20216/4/2021
CR-0012955Completed6/2/20216/4/2021
CR-0013001Completed6/4/20216/8/2021
CR-0013021Completed6/7/20216/9/2021
CR-0013014Completed6/7/20216/9/2021
CR-0013020Completed6/7/20216/8/2021
CR-0013047Completed6/9/20216/9/2021
CR-0013078In Progress6/11/2021null
CR-0013093On Hold6/14/2021null
CR-0013090Not Started6/14/2021null
CR-0013039Completed6/15/20216/9/2021
CR-0013142In Progress6/16/2021null

 

Expected results:

  Status <> CompletedCompleted after end of the monthTotal WIP at end of the Month
April224
May235
June606

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

PaulOlding_0-1624566152454.png

 

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.
PaulOlding_1-1624566422385.png

 

Helpful resources

Announcements
Microsoft Build 768x460.png

Microsoft Build is May 24-26. Have you registered yet?

Come together to explore latest innovations in code and application development—and gain insights from experts from around the world.

charticulator_carousel_with_text (1).png

Charticulator Design Challenge

Put your data visualization and design skills to the test! This exciting challenge is happening now through May 31st!

May UG Leader Call Carousel 768x460.png

What difference can a User Group make for you?

At the monthly call, connect with other leaders and find out how community makes your experience even better.