Hello!
I'm having trouble to DAX the scenario below
i have 2 tables
1- Date table -
2 Request Table - it tracks a Request from when it was recieved -> assigne to person -> when person start working on it -> when it gets sent to QC -> upto when it gets completed/resolved.
Two tables are related with Date[Date] and Request[Received Date].
Here is the problem. there is a back log (tickets that haven't been worked on yet)- and there are new requests that come in everyday.
i want to see if there is a way to show the rate of progress by which the back log is being cleared.
1)I need to produce a visualization that shows weekly progress - how many requests received each week, how many sent to QC each week and how many resolved each week
2) i need to produce a visualization that shows actual progress Vs planned progress (lets say if we want to complete the back log sooner that what would be with current rate) someting like this
Request Table: Example
Completed By | Request ID | Status | Received Date | Work Started Date | Sent to QC date | Resolved Date |
Ashley Morgan | 57927409 | Resolved | 12/14/2021 | 2/18/2022 | 3/29/2022 | 5/2/2022 |
Joe Andrew | 58035831 | Resolved | 12/14/2021 | 4/5/2022 | 4/5/2022 | 5/2/2022 |
Levy Anschti | 58099710 | Resolved | 12/14/2021 | 3/29/2022 | 3/29/2022 | 5/2/2022 |
Joe Andrew | 58099818 | Resolved | 12/16/2021 | 4/5/2022 | 4/6/2022 | 5/2/2022 |
Joe Andrew | 58516794 | Resolved | 12/16/2021 | 4/11/2022 | 4/26/2022 | 5/3/2022 |
Joe Andrew | 58501802 | Resolved | 12/16/2021 | 4/6/2022 | 4/6/2022 | 5/3/2022 |
Joe Andrew | 58521916 | Resolved | 12/16/2021 | 4/11/2022 | 4/11/2022 | 5/3/2022 |
Joe Andrew | 58498927 | Resolved | 12/16/2021 | 4/1/2022 | 4/1/2022 | 5/4/2022 |
Joe Andrew | 58522881 | Resolved | 12/16/2021 | 4/4/2022 | 4/4/2022 | 5/4/2022 |
Joe Andrew | 58516261 | Resolved | 12/16/2021 | 4/27/2022 | 5/12/2022 | 5/13/2022 |
Joe Andrew | 58528514 | Resolved | 12/17/2021 | 4/7/2022 | 4/7/2022 | 5/5/2022 |
Joe Andrew | 58528253 | Resolved | 12/17/2021 | 4/19/2022 | 4/20/2022 | 5/5/2022 |
Joe Andrew | 58527931 | Resolved | 12/17/2021 | 4/18/2022 | 4/18/2022 | 5/11/2022 |
Joe Andrew | 58491974 | Resolved | 12/20/2021 | 4/8/2022 | 4/11/2022 | 5/2/2022 |
Joe Andrew | 58498402 | Resolved | 12/20/2021 | 4/11/2022 | 4/11/2022 | 5/5/2022 |
Joe Andrew | 58535903 | Resolved | 12/20/2021 | 4/8/2022 | 4/8/2022 | 5/5/2022 |
Joe Andrew | 57464892 | Resolved | 12/20/2021 | 4/11/2022 | 4/11/2022 | 5/6/2022 |
Joe Andrew | 58535753 | Resolved | 12/21/2021 | 4/28/2022 | 4/28/2022 | 5/6/2022 |
Joe Andrew | 58099784 | Resolved | 12/21/2021 | 4/8/2022 | 4/8/2022 | 5/6/2022 |
Joe Andrew | 58545623 | Resolved | 12/21/2021 | 4/7/2022 | 4/7/2022 | 5/9/2022 |
Levy Anschti | 58305038 | Resolved | 12/21/2021 | 4/18/2022 | 4/18/2022 | 5/9/2022 |
Ashley Morgan | 58523065 | Resolved | 12/21/2021 | 3/21/2022 | 4/6/2022 | 5/11/2022 |
Joe Andrew | 58551947 | Resolved | 12/22/2021 | 4/12/2022 | 4/26/2022 | 5/9/2022 |
Ashley Morgan | 58411611 | Resolved | 12/29/2021 | 3/21/2022 | 3/21/2022 | 5/2/2022 |
Ashley Morgan | 58580376 | Resolved | 12/29/2021 | 3/21/2022 | 3/21/2022 | 5/2/2022 |
Ashley Morgan | 58060974 | Resolved | 12/29/2021 | 3/22/2022 | 3/22/2022 | 5/2/2022 |
Ashley Morgan | 58099928 | Resolved | 12/29/2021 | 3/23/2022 | 3/23/2022 | 5/2/2022 |
Ashley Morgan | 58460195 | Resolved | 12/29/2021 | 3/23/2022 | 3/23/2022 | 5/2/2022 |
Ashley Morgan | 58513196 | Resolved | 12/29/2021 | 3/22/2022 | 3/22/2022 | 5/2/2022 |
Ashley Morgan | 58558290 | Resolved | 12/29/2021 | 3/22/2022 | 3/22/2022 | 5/2/2022 |
Ashley Morgan | 58572207 | Resolved | 12/29/2021 | 3/22/2022 | 3/22/2022 | 5/2/2022 |
Ashley Morgan | 58435842 | Resolved | 12/29/2021 | 3/24/2022 | 3/24/2022 | 5/2/2022 |
Joe Andrew | 58558758 | Resolved | 12/29/2021 | 4/12/2022 | 4/12/2022 | 5/9/2022 |
Joe Andrew | 58580491 | Resolved | 12/29/2021 | 4/12/2022 | 4/12/2022 | 5/9/2022 |
Joe Andrew | 58567262 | Resolved | 12/29/2021 | 4/28/2022 | 5/2/2022 | 5/11/2022 |
Joe Andrew | 58580799 | Resolved | 12/29/2021 | 4/7/2022 | 4/7/2022 | 5/11/2022 |
Joe Andrew | 58529842 | Resolved | 12/29/2021 | 4/11/2022 | 4/26/2022 | 5/11/2022 |
Joe Andrew | 58099571 | Resolved | 12/29/2021 | 4/11/2022 | 4/13/2022 | 5/11/2022 |
Joe Andrew | 58561242 | Resolved | 12/29/2021 | 4/11/2022 | 4/11/2022 | 5/11/2022 |
Joe Andrew | 58482724 | Resolved | 12/29/2021 | 4/29/2022 | 4/29/2022 | 5/11/2022 |
Joe Andrew | 58539284 | Resolved | 12/29/2021 | 4/28/2022 | 5/17/2022 | 5/17/2022 |
Joe Andrew | 58497309 | Resolved | 12/29/2021 | 4/7/2022 | 4/7/2022 | 5/19/2022 |
Levy Anschti | 58461641 | Resolved | 12/29/2021 | 4/19/2022 | 4/19/2022 | 5/19/2022 |
Levy Anschti | 58461601 | Resolved | 12/29/2021 | 4/19/2022 | 4/19/2022 | 5/19/2022 |
Joe Andrew | 58315918 | Resolved | 12/29/2021 | 4/22/2022 | 5/16/2022 | 5/25/2022 |
Ashley Morgan | 58550885 | Resolved | 12/30/2021 | 3/23/2022 | 4/7/2022 | 5/3/2022 |
Ashley Morgan | 58569471 | Assigned | 1/6/2022 | 3/28/2022 | 3/28/2022 |
|
Ashley Morgan | 58558505 | Assigned | 1/6/2022 | 3/28/2022 | 3/28/2022 |
|
Levy Anschti | 58519712 | Assigned | 1/6/2022 | 4/21/2022 | 4/21/2022 |
|
Levy Anschti | 58625394 | Assigned | 1/6/2022 | 4/20/2022 | 4/20/2022 |
|
Joe Andrew | 58624605 | Assigned | 1/6/2022 | 4/28/2022 | 4/28/2022 |
|
Ashley Morgan | 58638203 | Assigned | 1/7/2022 | 3/28/2022 | 4/8/2022 |
|
Ashley Morgan | 58637706 | Assigned | 1/7/2022 | 4/6/2022 | 4/6/2022 |
|
Ashley Morgan | 58590517 | Assigned | 1/7/2022 | 3/28/2022 | 3/28/2022 |
|
Ashley Morgan | 58637244 | Assigned | 1/7/2022 | 3/28/2022 | 3/28/2022 |
|
Martha Drew | 58099879 | Assigned | 1/7/2022 | 4/25/2022 |
|
|
Martha Drew | 58376626 | Assigned | 1/7/2022 | 4/26/2022 |
|
|
Martha Drew | 58636804 | Assigned | 1/7/2022 | 4/21/2022 |
|
|
Joe Andrew | 58638370 | Assigned | 1/7/2022 | 4/13/2022 |
|
|
Levy Anschti | 58636552 | Assigned | 1/7/2022 |
|
|
|
Levy Anschti | 58637377 | Assigned | 1/7/2022 |
|
|
|
Ashley Morgan | 58645938 | Assigned | 1/10/2022 |
|
|
|
Levy Anschti | 58375556 | Assigned | 1/10/2022 |
|
|
|
Ashley Morgan | 58167500 | Assigned | 1/11/2022 |
|
|
|
Ashley Morgan | 58656851 | Assigned | 1/11/2022 |
|
|
|
Ashley Morgan | 58100047 | Assigned | 1/11/2022 |
|
|
|
Ashley Morgan | 58572145 | Assigned | 1/11/2022 |
|
|
|
Ashley Morgan | 58658274 | Assigned | 1/11/2022 |
|
|
|
Ashley Morgan | 56682809 | Assigned | 1/11/2022 |
|
|
|
Levy Anschti | 58257972 | Assigned | 1/11/2022 |
|
|
|
Levy Anschti | 58646972 | Assigned | 1/11/2022 |
|
|
|
Levy Anschti | 58657462 | Assigned | 1/11/2022 |
|
|
|
Levy Anschti | 58099402 | Assigned | 1/11/2022 |
|
|
|
Levy Anschti | 58519716 | Assigned | 1/11/2022 |
|
|
|
Levy Anschti | 58656847 | Assigned | 1/11/2022 |
|
|
|
Levy Anschti | 58661174 | Assigned | 1/11/2022 |
|
|
|
Levy Anschti | 56682807 | Assigned | 1/11/2022 |
|
|
|
Ashley Morgan | 58548061 | Assigned | 1/12/2022 |
|
|
|
Hi @One-Dash ,
1. Create a new table for the X-axis:
For X-axis = {"Total Requests for the week","Start to QC","QC to Resolved" }2. Get Year and Week in Dates table:
Dates =
DISTINCT (
SELECTCOLUMNS (
CALENDAR ( MIN ( 'Table1'[Received Date] ), MAX ( 'Table1'[Resolved Date] ) ),
"Year", YEAR ( [Date] ),
"Month", MONTH ( [Date] ),
"Week", WEEKNUM ( [Date], 2 ),
"Year Week",
YEAR ( [Date] ) & "/Week"
& WEEKNUM ( [Date], 2 )
)
)
3.Then create a measure:
Measure =
VAR _request =
CALCULATE (
DISTINCTCOUNT ( Table1[Request ID] ),
FILTER (
'Table1',
YEAR ( [Received Date] ) = MAX ( 'Dates'[Year] )
&& WEEKNUM ( [Received Date], 2 ) = MAX ( 'Dates'[Week] )
)
)
VAR _qc =
CALCULATE (
DISTINCTCOUNT ( Table1[Request ID] ),
FILTER (
'Table1',
YEAR ( [Sent to QC date] ) = MAX ( 'Dates'[Year] )
&& WEEKNUM ( [Sent to QC date], 2 ) = MAX ( 'Dates'[Week] )
)
)
VAR _resolved =
CALCULATE (
DISTINCTCOUNT ( Table1[Request ID] ),
FILTER (
'Table1',
YEAR ( [Resolved Date] ) = MAX ( 'Dates'[Year] )
&& WEEKNUM ( [Resolved Date], 2 ) = MAX ( 'Dates'[Week] )
)
)
RETURN
SWITCH (
MAX ( 'For X-axis'[Type] ),
"Total Requests for the week", _request,
"Start to QC", _qc,
"QC to Resolved", _resolved
)
Output:
Best Regards,
Eyelyn Qin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi @One-Dash ,
1. Create a new table for the X-axis:
For X-axis = {"Total Requests for the week","Start to QC","QC to Resolved" }2. Get Year and Week in Dates table:
Dates =
DISTINCT (
SELECTCOLUMNS (
CALENDAR ( MIN ( 'Table1'[Received Date] ), MAX ( 'Table1'[Resolved Date] ) ),
"Year", YEAR ( [Date] ),
"Month", MONTH ( [Date] ),
"Week", WEEKNUM ( [Date], 2 ),
"Year Week",
YEAR ( [Date] ) & "/Week"
& WEEKNUM ( [Date], 2 )
)
)
3.Then create a measure:
Measure =
VAR _request =
CALCULATE (
DISTINCTCOUNT ( Table1[Request ID] ),
FILTER (
'Table1',
YEAR ( [Received Date] ) = MAX ( 'Dates'[Year] )
&& WEEKNUM ( [Received Date], 2 ) = MAX ( 'Dates'[Week] )
)
)
VAR _qc =
CALCULATE (
DISTINCTCOUNT ( Table1[Request ID] ),
FILTER (
'Table1',
YEAR ( [Sent to QC date] ) = MAX ( 'Dates'[Year] )
&& WEEKNUM ( [Sent to QC date], 2 ) = MAX ( 'Dates'[Week] )
)
)
VAR _resolved =
CALCULATE (
DISTINCTCOUNT ( Table1[Request ID] ),
FILTER (
'Table1',
YEAR ( [Resolved Date] ) = MAX ( 'Dates'[Year] )
&& WEEKNUM ( [Resolved Date], 2 ) = MAX ( 'Dates'[Week] )
)
)
RETURN
SWITCH (
MAX ( 'For X-axis'[Type] ),
"Total Requests for the week", _request,
"Start to QC", _qc,
"QC to Resolved", _resolved
)
Output:
Best Regards,
Eyelyn Qin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Edit: the first visual would look something like this
Helpful resources
Power BI Monthly Update - November 2025
Check out the November 2025 Power BI update to learn about new features.
Fabric Data Days
Advance your Data & AI career with 50 days of live learning, contests, hands-on challenges, study groups & certifications and more!
FabCon Atlanta 2026
Join us at FabCon Atlanta, March 16-20, for the ultimate Fabric, Power BI, AI and SQL community-led event. Save $200 with code FABCOMM.
