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?