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
May 23 2022 epsiode 5 without aka link.jpg

The Power BI Community Show

Welcome to the Power BI Community Show! Jeroen ter Heerdt talks about the importance of Data Modeling.

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!

Power BI Dev Camp Session 22 with aka link 768x460.jpg

Check it out!

Mark your calendars and join us on Thursday, May 26 at 11a PDT for a great session with Ted Pattison!

Power BI Release May 2022 768x460.png

Check it out!

Click here to read more about the May 2022 updates!