cancel
Showing results for
Did you mean:
Frequent Visitor

## Calculate and visualize number of items done / planned and not done / planned and done per day

Hello all,

Could someone please help with the following issue that I have? (I am new to the community so please bare with me while I try to provide a comprehensive explanation)

In one table (ie Siebel Data), for each activity processed I have an Actual End Date and a Planned End Date. The activity Status can be either in progress (in this case the activity end date is blank) or done (in this case the activity end date is <> blank); all planned end dates are <> blank. On top of that, each activity is of a certain Type.

I created a new table (ie Date Dimension) in order to add some time intelligence to the power BI model (it includes all the dates between the min actual end date and the max planned end date). The two tables are linked together.

Now for the issue... I am trying to create a visual (as shown below) that for each date in the Date Dimension table shows:

• # of activities done (no issues here - in the Siebel Data table I created a calculated column, # Done = if('Siebel Data'[Status]="Done",1,0), which I sum on the visual),
• # of activities planned which are not yet done (no issues here - in the Activity Data table I created a calculated column, # Done = if('Siebel Data'[Status]="Done",0,1), which I sum on the visual) and
• # of activities planned which are done (this is supposed to show, for all activities with Status="Done", how many were planned each day)

In the same time I want this visual to work with two slicers (one for date - no issue here - and one for activity type).

I tried creating a calculated column (as shown on the right) in the Date Dimension table that calculates the number of activities planned which are done for each day but this doesn't work with the activity type slicer or any (other than the date) slicer...

I really appreciate you help!

Kind regards,

1 ACCEPTED SOLUTION

Accepted Solutions
Moderator

## Re: Calculate and visualize number of items done / planned and not done / planned and done per day

Create inactive relationship between date dimension and Siebel Data using Date field and Planned End field, then create the following measure in  your Siebel Data table and check if the measure returns your expected result.

Measure  = CALCULATE(COUNTA('Siebel Data'[Activity #]),USERELATIONSHIP('Date Dimension'[Date],'Siebel Data'[Planned End]),FILTER('Siebel Data','Siebel Data'[Status]="Done"))

Regards,
Lydia

Community Support Team _ Lydia Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
5 REPLIES 5
Super User

## Re: Calculate and visualize number of items done / planned and not done / planned and done per day

Please post mock/sample data that can easily be copied. It sounds like you need to turn your calculated column into a measure, but can't work through that without data to mess around with.

Proud to be a Datanaut!

Frequent Visitor

## Re: Calculate and visualize number of items done / planned and not done / planned and done per day

Hello and thank you for your reply. Unfortunately I cannot attach a file here nor can I use any file hosting service so I appologize for pasting the data here.

Siebel Data (this is linked to the date intel table through the Act End / Planned column, which is calculated using Act End / Planned = IF('Siebel Data'[Status]="Done",'Siebel Data'[Act End].[Date],'Siebel Data'[Pl End].[Date]))

 Activity # Activity Type Status Planned End Actual End Team Rollup Act End - Short Year Act End - Quarter Act End - Month Act End - Week Act End - Day Y,Q,M,W,D Done / Planned Pl End - Short Year Pl End - Quarter Pl End - Month Pl End - Week Pl End - Day Y,Q,M,W,D(Pl) Act End / Planned # Done # Planned 1-6XUMDKH Other Done 12/30/2016 5:00 12/28/2016 17:15 Projects 16 Q4 Dec W53 28 16,Q4,Dec,W53,28 Done 16 Q4 Dec W53 30 16,Q4,Dec,W53,30 #### 1 0 1-7DZLI7X Other Done 3/14/2017 20:59 3/14/2017 8:33 Contracts 17 Q1 Mar W12 14 17,Q1,Mar,W12,14 Done 17 Q1 Mar W12 14 17,Q1,Mar,W12,14 #### 1 0 1-7IC94FF PR-R In Progress 3/17/2017 7:00 Projects Queue Q W ,Q,,W, Planned End 17 Q1 Mar W12 17 17,Q1,Mar,W12,17 #### 0 1 1-7KZJX9N PR In Progress 4/4/2017 7:00 Projects Queue Q W ,Q,,W, Planned End 17 Q2 Apr W15 4 17,Q2,Apr,W15,04 #### 0 1 1-7LQFWEV Other Done 4/10/2017 7:43 4/12/2017 10:22 Contracts 17 Q2 Apr W16 12 17,Q2,Apr,W16,12 Done 17 Q2 Apr W16 10 17,Q2,Apr,W16,10 #### 1 0 1-7LVODPH PR-BO-R Done 4/14/2017 5:00 4/13/2017 13:21 Spot 17 Q2 Apr W16 13 17,Q2,Apr,W16,13 Done 17 Q2 Apr W16 14 17,Q2,Apr,W16,14 #### 1 0 1-7MKAFCF PR-R In Progress 4/21/2017 7:00 Projects Queue Q W ,Q,,W, Planned End 17 Q2 Apr W17 21 17,Q2,Apr,W17,21 #### 0 1 1-7MLNBZE PR-BO-R Done 4/28/2017 12:08 4/25/2017 16:52 Spot 17 Q2 Apr W18 25 17,Q2,Apr,W18,25 Done 17 Q2 Apr W18 28 17,Q2,Apr,W18,28 #### 1 0 1-7N50WJ0 PR-BO-R Done 4/26/2017 12:32 4/25/2017 9:44 Parts 17 Q2 Apr W18 25 17,Q2,Apr,W18,25 Done 17 Q2 Apr W18 26 17,Q2,Apr,W18,26 #### 1 0 1-7U1699C BOOKING Done 5/8/2017 12:09 5/5/2017 12:28 Spot 17 Q2 May W19 5 17,Q2,May,W19,05 Done 17 Q2 May W20 8 17,Q2,May,W20,08 #### 1 0 1-7O7FHJ4 PR-BO-R Done 5/9/2017 16:55 5/9/2017 14:14 Spot 17 Q2 May W20 9 17,Q2,May,W20,09 Done 17 Q2 May W20 9 17,Q2,May,W20,09 #### 1 0 1-7W654GB PR-BO-R Done 5/16/2017 7:40 5/9/2017 7:47 Parts 17 Q2 May W20 9 17,Q2,May,W20,09 Done 17 Q2 May W21 16 17,Q2,May,W21,16 #### 1 0 1-7U0VAE8 PR-BO-R Done 5/12/2017 5:00 5/11/2017 11:36 Spot 17 Q2 May W20 11 17,Q2,May,W20,11 Done 17 Q2 May W20 12 17,Q2,May,W20,12 #### 1 0 1-7TONT17 PR-S Not Started 5/12/2017 14:26 Projects Q W ,Q,,W, Planned End 17 Q2 May W20 12 17,Q2,May,W20,12 #### 0 1 1-7VIR6YX PR-S Not Started 5/12/2017 20:16 Projects Q W ,Q,,W, Planned End 17 Q2 May W20 12 17,Q2,May,W20,12 #### 0 1 1-7W6VKF7 PR-R Done 5/20/2017 6:09 5/16/2017 14:23 Cyber 17 Q2 May W21 16 17,Q2,May,W21,16 Done 17 Q2 May W21 20 17,Q2,May,W21,20 #### 1 0 1-7W6I7NS PR-S Not Started 5/16/2017 16:30 Spot Q W ,Q,,W, Planned End 17 Q2 May W21 16 17,Q2,May,W21,16 #### 0 1 1-7XG919L PR-S Not Started 5/19/2017 15:08 Projects Q W ,Q,,W, Planned End 17 Q2 May W21 19 17,Q2,May,W21,19 #### 0 1 1-7XH2IB8 PR-S Not Started 5/23/2017 9:00 Projects Q W ,Q,,W, Planned End 17 Q2 May W22 23 17,Q2,May,W22,23 #### 0 1 1-7XH4FH6 PR-S Not Started 5/23/2017 14:39 Parts Q W ,Q,,W, Planned End 17 Q2 May W22 23 17,Q2,May,W22,23 #### 0 1 1-7WLNWFJ PR-BO-R Done 5/26/2017 15:00 5/25/2017 13:02 Spot 17 Q2 May W22 25 17,Q2,May,W22,25 Done 17 Q2 May W22 26 17,Q2,May,W22,26 #### 1 0 1-7XZ2SMC PR-BO-R Done 6/1/2017 12:57 5/26/2017 7:18 Cyber 17 Q2 May W22 26 17,Q2,May,W22,26 Done 17 Q2 Jun W23 1 17,Q2,Jun,W23,01 #### 1 0 1-7XV46XH PR-S Not Started 5/26/2017 15:43 Spot Q W ,Q,,W, Planned End 17 Q2 May W22 26 17,Q2,May,W22,26 #### 0 1 1-7Y26AC6 PR-BO-R Done 5/31/2017 9:10 5/26/2017 12:57 Projects 17 Q2 May W22 26 17,Q2,May,W22,26 Done 17 Q2 May W23 31 17,Q2,May,W23,31 #### 1 0 1-7YKA16X PR-S Not Started 5/29/2017 7:19 Projects Q W ,Q,,W, Planned End 17 Q2 May W23 29 17,Q2,May,W23,29 #### 0 1 1-7XVCHLP PR-BO-R Done 5/31/2017 14:47 5/31/2017 12:13 Projects 17 Q2 May W23 31 17,Q2,May,W23,31 Done 17 Q2 May W23 31 17,Q2,May,W23,31 #### 1 0 1-7XK2BTW PR-BO-R Done 6/1/2017 12:09 5/31/2017 12:21 Projects 17 Q2 May W23 31 17,Q2,May,W23,31 Done 17 Q2 Jun W23 1 17,Q2,Jun,W23,01 #### 1 0 1-7WDQS2R PR-BO-R Done 5/31/2017 15:50 5/31/2017 11:15 Projects 17 Q2 May W23 31 17,Q2,May,W23,31 Done 17 Q2 May W23 31 17,Q2,May,W23,31 #### 1 0 1-7ZZB48O PR-S Not Started 6/5/2017 13:13 Projects Q W ,Q,,W, Planned End 17 Q2 Jun W24 5 17,Q2,Jun,W24,05 #### 0 1 1-81COCR7 PR-BO Done 6/7/2017 7:22 6/6/2017 13:08 Parts 17 Q2 Jun W24 6 17,Q2,Jun,W24,06 Done 17 Q2 Jun W24 7 17,Q2,Jun,W24,07 #### 1 0 1-7ZOVHY3 PR-S Not Started 6/6/2017 14:23 Projects Q W ,Q,,W, Planned End 17 Q2 Jun W24 6 17,Q2,Jun,W24,06 #### 0 1 1-7ZMEIZP PR-BO-R Done 6/7/2017 18:00 6/7/2017 14:48 Cyber 17 Q2 Jun W24 7 17,Q2,Jun,W24,07 Done 17 Q2 Jun W24 7 17,Q2,Jun,W24,07 #### 1 0 1-7WO3ICL PR-BO-R Done 6/9/2017 19:51 6/9/2017 8:42 Cyber 17 Q2 Jun W24 9 17,Q2,Jun,W24,09 Done 17 Q2 Jun W24 9 17,Q2,Jun,W24,09 #### 1 0 1-7X9CS3B PR Done 5/23/2017 18:30 6/9/2017 7:44 Contracts 17 Q2 Jun W24 9 17,Q2,Jun,W24,09 Done 17 Q2 May W22 23 17,Q2,May,W22,23 #### 1 0 1-81LSHQF PR-BO-R Done 6/12/2017 5:00 6/9/2017 15:49 Spot 17 Q2 Jun W24 9 17,Q2,Jun,W24,09 Done 17 Q2 Jun W25 12 17,Q2,Jun,W25,12 #### 1 0 1-81N760V PR-BO-R Done 6/14/2017 19:37 6/13/2017 14:12 Projects 17 Q2 Jun W25 13 17,Q2,Jun,W25,13 Done 17 Q2 Jun W25 14 17,Q2,Jun,W25,14 #### 1 0 1-81PFI12 PR-S Not Started 6/14/2017 14:37 Projects Q W ,Q,,W, Planned End 17 Q2 Jun W25 14 17,Q2,Jun,W25,14 #### 0 1 1-7ZSNGF7 PR-BO-R Done 6/14/2017 20:00 6/14/2017 19:47 Projects 17 Q2 Jun W25 14 17,Q2,Jun,W25,14 Done 17 Q2 Jun W25 14 17,Q2,Jun,W25,14 #### 1 0 1-81PLHS2 PR-BO-R Done 6/30/2017 13:27 6/15/2017 8:16 Projects 17 Q2 Jun W25 15 17,Q2,Jun,W25,15 Done 17 Q2 Jun W27 30 17,Q2,Jun,W27,30 #### 1 0 1-828R15U PR-BO-R Done 6/16/2017 16:25 6/16/2017 15:03 Spot 17 Q2 Jun W25 16 17,Q2,Jun,W25,16 Done 17 Q2 Jun W25 16 17,Q2,Jun,W25,16 #### 1 0 1-82K6YQ8 PR-BO-R Done 6/21/2017 8:47 6/16/2017 8:48 Parts 17 Q2 Jun W25 16 17,Q2,Jun,W25,16 Done 17 Q2 Jun W26 21 17,Q2,Jun,W26,21 #### 1 0 1-829NE1A PR-BO-R Done 6/19/2017 11:00 6/16/2017 16:55 Projects 17 Q2 Jun W25 16 17,Q2,Jun,W25,16 Done 17 Q2 Jun W26 19 17,Q2,Jun,W26,19 #### 1 0 1-7YEG3L1 PR-BO-R Done 6/23/2017 14:15 6/19/2017 7:51 Projects 17 Q2 Jun W26 19 17,Q2,Jun,W26,19 Done 17 Q2 Jun W26 23 17,Q2,Jun,W26,23 #### 1 0 1-831RNZD PR-BO-R Done 6/27/2017 14:12 6/20/2017 14:30 Cyber 17 Q2 Jun W26 20 17,Q2,Jun,W26,20 Done 17 Q2 Jun W27 27 17,Q2,Jun,W27,27 #### 1 0 1-81SUF0J PR-BO-R Done 6/22/2017 23:00 6/21/2017 10:58 Projects 17 Q2 Jun W26 21 17,Q2,Jun,W26,21 Done 17 Q2 Jun W26 22 17,Q2,Jun,W26,22 #### 1 0 1-7X7UIPL PR-BO-R Done 6/23/2017 8:12 6/21/2017 7:05 Projects 17 Q2 Jun W26 21 17,Q2,Jun,W26,21 Done 17 Q2 Jun W26 23 17,Q2,Jun,W26,23 #### 1 0 1-81CTMO5 PR-BO-R Done 6/23/2017 16:52 6/21/2017 12:48 Projects 17 Q2 Jun W26 21 17,Q2,Jun,W26,21 Done 17 Q2 Jun W26 23 17,Q2,Jun,W26,23 #### 1 0 1-7Y5JW5J PR-BO-R Done 6/27/2017 23:00 6/22/2017 14:02 Projects 17 Q2 Jun W26 22 17,Q2,Jun,W26,22 Done 17 Q2 Jun W27 27 17,Q2,Jun,W27,27 #### 1 0 1-81PTICJ PR-BO-R Done 6/23/2017 15:08 6/22/2017 13:00 Projects 17 Q2 Jun W26 22 17,Q2,Jun,W26,22 Done 17 Q2 Jun W26 23 17,Q2,Jun,W26,23 #### 1 0
Moderator

## Re: Calculate and visualize number of items done / planned and not done / planned and done per day

I am not quite sure about the logic that you use to calculate “# of activities planned which are done”, could you please describe more details and post the expected result for those all activities with Status="Done"?

Additionally, what is the result after you remove ALL() function from your current DAX formula?

Regards,
Lydia

Community Support Team _ Lydia Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Frequent Visitor

## Re: Calculate and visualize number of items done / planned and not done / planned and done per day

Let me describe the logic that I want to use... For each calendar date in the Date Dimension table I want to show the following:

- number of activities that have an Actual End equal to the calendar date (# of activities done per day)

- number of activities that are not yet done and that have a Planned End equal to the calendar date (number of activities that are planned)

- number of activities that are done and had a Planned End equal to the calendar date (number of activities that were planned per day)

And as described in my original post, I want this to work with the following slicers: Date, Activity Type and Team Rollup.

Removing the ALL() function gives the same results, which are actually correct. But when I show them on the column chart visual no filters work (visual filter or slicer) - it shows the same number of activities regardless of the team or the activity type selected.

Moderator