Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

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 @Anonymous 

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.

 

Anonymous
Not applicable

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
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors