cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Sha
Helper II
Helper II

Cumulative count of records for time period

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. 

1 ACCEPTED SOLUTION

Any experts that can help me?

View solution in original post

11 REPLIES 11
Ashish_Mathur
Super User III
Super User III

Hi,

Share some data (which i can paste in an Excel file) and show the expected result in a simple Table format.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

Thank you for your help.  I'm not sure how to send the file, so here is a snapshot:

fldIdfldCreationDatefldCompletionDateDash-ActCompletionYearMoDash-ActCreationYearMofldStatus
97/2/187/2/182018-072018-073
107/2/18 Ongoing2018-072
117/2/187/2/182018-072018-073
127/2/187/2/182018-072018-073
137/2/187/2/182018-072018-073
147/2/18 Ongoing2018-072
157/2/18 Ongoing2018-072
167/6/187/10/182018-072018-073
177/9/182/20/192019-022018-073
187/11/1812/20/182018-122018-073
257/12/189/20/182018-092018-073
207/13/18 Ongoing2018-072
197/13/185/7/192019-052018-073
217/16/1812/20/182018-122018-073
227/16/1810/24/182018-102018-073
247/18/18 Ongoing2018-077
237/18/18 Ongoing2018-072
267/19/188/20/182018-082018-073
277/23/189/5/182018-092018-073
5837/24/18 Ongoing2018-072
287/24/188/17/182018-082018-073
307/24/18 Ongoing2018-077
327/26/188/14/182018-082018-073
317/26/189/10/182018-092018-073
337/27/188/7/182018-082018-073
597/27/188/21/182018-082018-073
347/27/188/7/182018-082018-073
357/27/18 Ongoing2018-077
367/27/187/31/182018-072018-073
377/30/18 Ongoing2018-072
387/30/188/20/182018-082018-073
417/31/188/6/182018-082018-073
397/31/189/21/182018-092018-073
407/31/18 Ongoing2018-072
427/31/18 Ongoing2018-072

 

Pivot, where fldStatus <> 7

Completed:    Created:   Pending: 
fldStatus(Multiple Items)   fldStatus(Multiple Items)    
  sum    sum  Difference
       
2018-0766  2018-073232 2018-0726
2018-081521  2018-086294 2018-0873
2018-092950  2018-0942136 2018-0986
2018-103282  2018-1036172 2018-1090
2018-1135117  2018-1159231 2018-11114
2018-1246163  2018-1234265 2018-12102
2019-0140203  2019-0185350 2019-01147
2019-0248251  2019-0268418 2019-02167
2019-0352303  2019-0379497 2019-03194
2019-0457360  2019-0461558 2019-04198
2019-0575435  2019-0566624 2019-05189
2019-0638473  2019-0662686 2019-06213
2019-0728501  2019-0735721 2019-07220
Ongoing220   (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.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

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.

Untitled.png


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

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]

 
This is the Measure that is NOT working...
Cumulative Pending: PendingRunningTotal = CALCULATE([WFPending],filter(all('CODE Workflow'[Dash-ActCreationYearMo]),'CODE Workflow'[Dash-ActCreationYearMo] <= max('CODE Workflow'[Dash-ActCreationYearMo])))

 

 

YearMonthWFTotalWFCompletedWFPendingPendingRunningTotalPending Running Total Should be
2018July326262626
2018August6215474773
2018September4229131386
2018October36324490
2018November59352424114
2018December3446-12-12102
2019January85404545147
2019February68482020167
2019March79522727194
2019April615744198
2019May6675-9-9189
2019June62382424213
2019July352877220

 

Any experts that can help me?

View solution in original post

Anonymous
Not applicable

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

Visual2.PNG  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.

WFTotal = countrows('CODE Workflow')
WFCompleted = CALCULATE([WFTotal],USERELATIONSHIP('Date'[DashDate],'CODE Workflow'[Dash-ActCompletionYearMo]))
WFPending = [WFTotal]-[WFCompleted]
Anonymous
Not applicable

@Sha 

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)))

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group!

MBAS on Demand

Microsoft Business Applications Summit sessions

On-demand access to all the great content presented by the product teams and community members! #MSBizAppsSummit #CommunityRocks

MBAS Attendee Badge

Claim Your Badge & Digital Swag!

Check out how to claim yours today!

Top Solution Authors
Top Kudoed Authors