Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
I have a table with created date and completion date. I have a date table where I have relationships and able to show in the line graph the count of records created in that month and the count of records completed in that month. I am looking for a way to get the Pending records (and show in same visual) but having trouble figuring out how to code for it. July has 32 created and 6 completed, that leaves 26 Pending. August has 62 created and 15 completed which leaves 47 Pending. However in August I should be showing all Pending (32+62=94 created; 6+15=21 completed which leaves 94-21= 73 Pending). If September has 0 created, 10 completed, then my Pending number is 32+62+0=94 created; 6+15+10=31 completed which leaves 94-31=63.
Solved! Go to Solution.
Hi,
Share some data (which i can paste in an Excel file) and show the expected result in a simple Table format.
Thank you for your help. I'm not sure how to send the file, so here is a snapshot:
fldId | fldCreationDate | fldCompletionDate | Dash-ActCompletionYearMo | Dash-ActCreationYearMo | fldStatus |
9 | 7/2/18 | 7/2/18 | 2018-07 | 2018-07 | 3 |
10 | 7/2/18 | Ongoing | 2018-07 | 2 | |
11 | 7/2/18 | 7/2/18 | 2018-07 | 2018-07 | 3 |
12 | 7/2/18 | 7/2/18 | 2018-07 | 2018-07 | 3 |
13 | 7/2/18 | 7/2/18 | 2018-07 | 2018-07 | 3 |
14 | 7/2/18 | Ongoing | 2018-07 | 2 | |
15 | 7/2/18 | Ongoing | 2018-07 | 2 | |
16 | 7/6/18 | 7/10/18 | 2018-07 | 2018-07 | 3 |
17 | 7/9/18 | 2/20/19 | 2019-02 | 2018-07 | 3 |
18 | 7/11/18 | 12/20/18 | 2018-12 | 2018-07 | 3 |
25 | 7/12/18 | 9/20/18 | 2018-09 | 2018-07 | 3 |
20 | 7/13/18 | Ongoing | 2018-07 | 2 | |
19 | 7/13/18 | 5/7/19 | 2019-05 | 2018-07 | 3 |
21 | 7/16/18 | 12/20/18 | 2018-12 | 2018-07 | 3 |
22 | 7/16/18 | 10/24/18 | 2018-10 | 2018-07 | 3 |
24 | 7/18/18 | Ongoing | 2018-07 | 7 | |
23 | 7/18/18 | Ongoing | 2018-07 | 2 | |
26 | 7/19/18 | 8/20/18 | 2018-08 | 2018-07 | 3 |
27 | 7/23/18 | 9/5/18 | 2018-09 | 2018-07 | 3 |
583 | 7/24/18 | Ongoing | 2018-07 | 2 | |
28 | 7/24/18 | 8/17/18 | 2018-08 | 2018-07 | 3 |
30 | 7/24/18 | Ongoing | 2018-07 | 7 | |
32 | 7/26/18 | 8/14/18 | 2018-08 | 2018-07 | 3 |
31 | 7/26/18 | 9/10/18 | 2018-09 | 2018-07 | 3 |
33 | 7/27/18 | 8/7/18 | 2018-08 | 2018-07 | 3 |
59 | 7/27/18 | 8/21/18 | 2018-08 | 2018-07 | 3 |
34 | 7/27/18 | 8/7/18 | 2018-08 | 2018-07 | 3 |
35 | 7/27/18 | Ongoing | 2018-07 | 7 | |
36 | 7/27/18 | 7/31/18 | 2018-07 | 2018-07 | 3 |
37 | 7/30/18 | Ongoing | 2018-07 | 2 | |
38 | 7/30/18 | 8/20/18 | 2018-08 | 2018-07 | 3 |
41 | 7/31/18 | 8/6/18 | 2018-08 | 2018-07 | 3 |
39 | 7/31/18 | 9/21/18 | 2018-09 | 2018-07 | 3 |
40 | 7/31/18 | Ongoing | 2018-07 | 2 | |
42 | 7/31/18 | Ongoing | 2018-07 | 2 |
Pivot, where fldStatus <> 7
Completed: | Created: | Pending: | ||||||||
fldStatus | (Multiple Items) | fldStatus | (Multiple Items) | |||||||
sum | sum | Difference | ||||||||
2018-07 | 6 | 6 | 2018-07 | 32 | 32 | 2018-07 | 26 | |||
2018-08 | 15 | 21 | 2018-08 | 62 | 94 | 2018-08 | 73 | |||
2018-09 | 29 | 50 | 2018-09 | 42 | 136 | 2018-09 | 86 | |||
2018-10 | 32 | 82 | 2018-10 | 36 | 172 | 2018-10 | 90 | |||
2018-11 | 35 | 117 | 2018-11 | 59 | 231 | 2018-11 | 114 | |||
2018-12 | 46 | 163 | 2018-12 | 34 | 265 | 2018-12 | 102 | |||
2019-01 | 40 | 203 | 2019-01 | 85 | 350 | 2019-01 | 147 | |||
2019-02 | 48 | 251 | 2019-02 | 68 | 418 | 2019-02 | 167 | |||
2019-03 | 52 | 303 | 2019-03 | 79 | 497 | 2019-03 | 194 | |||
2019-04 | 57 | 360 | 2019-04 | 61 | 558 | 2019-04 | 198 | |||
2019-05 | 75 | 435 | 2019-05 | 66 | 624 | 2019-05 | 189 | |||
2019-06 | 38 | 473 | 2019-06 | 62 | 686 | 2019-06 | 213 | |||
2019-07 | 28 | 501 | 2019-07 | 35 | 721 | 2019-07 | 220 | |||
Ongoing | 220 | (blank) | ||||||||
(blank) | ||||||||||
Hi,
For fldID 22, where the created date is July and completed date is October, should this ID be counted during the intervening months i.e. from August to September as created or should it just be left out for August and September.
It would be counted as Created in the month created (July) and counted as Closed in the month it closed (Oct) and would be counted as Pending in the months (Aug-Sep)
Hi,
You may download my PBI file from here.
Hope this helps.
Thanks for your help but that doesn't work for me as the table layout is different. My table is one row with both Created and Completed date. And I have Relationship between my Date table with 'Code Workflow' table. Active is with Dash-ActCreationYearMo and Inactive is with Dash-ActCompletionYearMo.
I have 3 Measures that are working:
Created: WFTotal = countrows('CODE Workflow')
Completed: WFCompleted = CALCULATE([WFTotal],USERELATIONSHIP('Date'[DashDate],'CODE Workflow'[Dash-ActCompletionYearMo]))
Pending: WFPending = [WFTotal]-[WFCompleted]
Year | Month | WFTotal | WFCompleted | WFPending | PendingRunningTotal | Pending Running Total Should be |
2018 | July | 32 | 6 | 26 | 26 | 26 |
2018 | August | 62 | 15 | 47 | 47 | 73 |
2018 | September | 42 | 29 | 13 | 13 | 86 |
2018 | October | 36 | 32 | 4 | 4 | 90 |
2018 | November | 59 | 35 | 24 | 24 | 114 |
2018 | December | 34 | 46 | -12 | -12 | 102 |
2019 | January | 85 | 40 | 45 | 45 | 147 |
2019 | February | 68 | 48 | 20 | 20 | 167 |
2019 | March | 79 | 52 | 27 | 27 | 194 |
2019 | April | 61 | 57 | 4 | 4 | 198 |
2019 | May | 66 | 75 | -9 | -9 | 189 |
2019 | June | 62 | 38 | 24 | 24 | 213 |
2019 | July | 35 | 28 | 7 | 7 | 220 |
Any experts that can help me?
Hi @Sha
Your calculation seems alright to me, since you're playing with two dates make sure there is one active relationship and one inactive relationship for the date table. I think you need to create 2 measures and get the difference to show your outstanding records. If you can show me your current visual that would help us what you really want to do.
Thanks
I have 2 measures which look correct (Created and Completed), my measure for Pending (measure for created - measure for completed) is giving me what is Pending in the month but I need the difference of Total Cumlative Created - Total Cumlative Completed by month.
a simple calculation will get you the YTD summation. you need to use following formula
https://docs.microsoft.com/en-us/dax/datesytd-function-dax
=Calculate(sum( your total),DATESYTD(DateTime[DateKey]))
Thanks
My measures are counting the rows, so I have nothing to sum. I'm wanting to sum up the measure but that doesn't work as it won't let me create measure and sum another measure.
I was able to create 2 new tables but it's not working quite right:
WFCompletedCounts = SUMMARIZE('CODE Workflow','CODE Workflow'[Dash-ActCompletionYearMo],
"WFCompTotal",countrows('CODE Workflow'),
"WFCompCumTotal",CALCULATE(countrows('CODE Workflow'),FILTER (
ALL ('CODE Workflow'),
'CODE Workflow'[Dash-ActCompletionYearMo] <= MAX ( 'CODE Workflow'[Dash-ActCompletionYearMo]) && 'CODE Workflow'[fldStatus] <> 7)))
WFCreatedCounts = SUMMARIZE('CODE Workflow','CODE Workflow'[Dash-ActCreationYearMo],
"WFCreatedTotal",countrows('CODE Workflow'), --I can't get this to filter out the fldStatus like below
"WFCreatedCumTotal",CALCULATE(countrows('CODE Workflow'),FILTER (
ALL ('CODE Workflow'),
'CODE Workflow'[fldCreationDate] <= MAX ( 'CODE Workflow'[fldCreationDate]) && 'CODE Workflow'[fldStatus] <> 7)))
User | Count |
---|---|
102 | |
88 | |
78 | |
71 | |
69 |
User | Count |
---|---|
113 | |
99 | |
97 | |
72 | |
68 |