Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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:
Solved! Go to Solution.
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.
Hi @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 |
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.
User | Count |
---|---|
42 | |
28 | |
23 | |
18 | |
15 |
User | Count |
---|---|
54 | |
35 | |
18 | |
18 | |
15 |