cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
RaduOrzata Frequent Visitor
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).

Capture.JPG 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

Capture1.JPG

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,

Radu

1 ACCEPTED SOLUTION

Accepted Solutions
Moderator v-yuezhe-msft
Moderator

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

@RaduOrzata,

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


Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!


RaduOrzata Frequent Visitor
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 TypeStatusPlanned EndActual EndTeam RollupAct End - Short YearAct End - QuarterAct End - MonthAct End - WeekAct End - DayY,Q,M,W,DDone / PlannedPl End - Short YearPl End - QuarterPl End - MonthPl End - WeekPl End - DayY,Q,M,W,D(Pl)Act End / Planned# Done# Planned
1-6XUMDKHOtherDone12/30/2016 5:0012/28/2016 17:15Projects16Q4DecW532816,Q4,Dec,W53,28Done16Q4DecW533016,Q4,Dec,W53,30####10
1-7DZLI7XOtherDone3/14/2017 20:593/14/2017 8:33Contracts17Q1MarW121417,Q1,Mar,W12,14Done17Q1MarW121417,Q1,Mar,W12,14####10
1-7IC94FFPR-RIn Progress3/17/2017 7:00 Projects Queue Q W ,Q,,W,Planned End17Q1MarW121717,Q1,Mar,W12,17####01
1-7KZJX9NPRIn Progress4/4/2017 7:00 Projects Queue Q W ,Q,,W,Planned End17Q2AprW15417,Q2,Apr,W15,04####01
1-7LQFWEVOtherDone4/10/2017 7:434/12/2017 10:22Contracts17Q2AprW161217,Q2,Apr,W16,12Done17Q2AprW161017,Q2,Apr,W16,10####10
1-7LVODPHPR-BO-RDone4/14/2017 5:004/13/2017 13:21Spot17Q2AprW161317,Q2,Apr,W16,13Done17Q2AprW161417,Q2,Apr,W16,14####10
1-7MKAFCFPR-RIn Progress4/21/2017 7:00 Projects Queue Q W ,Q,,W,Planned End17Q2AprW172117,Q2,Apr,W17,21####01
1-7MLNBZEPR-BO-RDone4/28/2017 12:084/25/2017 16:52Spot17Q2AprW182517,Q2,Apr,W18,25Done17Q2AprW182817,Q2,Apr,W18,28####10
1-7N50WJ0PR-BO-RDone4/26/2017 12:324/25/2017 9:44Parts17Q2AprW182517,Q2,Apr,W18,25Done17Q2AprW182617,Q2,Apr,W18,26####10
1-7U1699CBOOKINGDone5/8/2017 12:095/5/2017 12:28Spot17Q2MayW19517,Q2,May,W19,05Done17Q2MayW20817,Q2,May,W20,08####10
1-7O7FHJ4PR-BO-RDone5/9/2017 16:555/9/2017 14:14Spot17Q2MayW20917,Q2,May,W20,09Done17Q2MayW20917,Q2,May,W20,09####10
1-7W654GBPR-BO-RDone5/16/2017 7:405/9/2017 7:47Parts17Q2MayW20917,Q2,May,W20,09Done17Q2MayW211617,Q2,May,W21,16####10
1-7U0VAE8PR-BO-RDone5/12/2017 5:005/11/2017 11:36Spot17Q2MayW201117,Q2,May,W20,11Done17Q2MayW201217,Q2,May,W20,12####10
1-7TONT17PR-SNot Started5/12/2017 14:26 Projects Q W ,Q,,W,Planned End17Q2MayW201217,Q2,May,W20,12####01
1-7VIR6YXPR-SNot Started5/12/2017 20:16 Projects Q W ,Q,,W,Planned End17Q2MayW201217,Q2,May,W20,12####01
1-7W6VKF7PR-RDone5/20/2017 6:095/16/2017 14:23Cyber17Q2MayW211617,Q2,May,W21,16Done17Q2MayW212017,Q2,May,W21,20####10
1-7W6I7NSPR-SNot Started5/16/2017 16:30 Spot Q W ,Q,,W,Planned End17Q2MayW211617,Q2,May,W21,16####01
1-7XG919LPR-SNot Started5/19/2017 15:08 Projects Q W ,Q,,W,Planned End17Q2MayW211917,Q2,May,W21,19####01
1-7XH2IB8PR-SNot Started5/23/2017 9:00 Projects Q W ,Q,,W,Planned End17Q2MayW222317,Q2,May,W22,23####01
1-7XH4FH6PR-SNot Started5/23/2017 14:39 Parts Q W ,Q,,W,Planned End17Q2MayW222317,Q2,May,W22,23####01
1-7WLNWFJPR-BO-RDone5/26/2017 15:005/25/2017 13:02Spot17Q2MayW222517,Q2,May,W22,25Done17Q2MayW222617,Q2,May,W22,26####10
1-7XZ2SMCPR-BO-RDone6/1/2017 12:575/26/2017 7:18Cyber17Q2MayW222617,Q2,May,W22,26Done17Q2JunW23117,Q2,Jun,W23,01####10
1-7XV46XHPR-SNot Started5/26/2017 15:43 Spot Q W ,Q,,W,Planned End17Q2MayW222617,Q2,May,W22,26####01
1-7Y26AC6PR-BO-RDone5/31/2017 9:105/26/2017 12:57Projects17Q2MayW222617,Q2,May,W22,26Done17Q2MayW233117,Q2,May,W23,31####10
1-7YKA16XPR-SNot Started5/29/2017 7:19 Projects Q W ,Q,,W,Planned End17Q2MayW232917,Q2,May,W23,29####01
1-7XVCHLPPR-BO-RDone5/31/2017 14:475/31/2017 12:13Projects17Q2MayW233117,Q2,May,W23,31Done17Q2MayW233117,Q2,May,W23,31####10
1-7XK2BTWPR-BO-RDone6/1/2017 12:095/31/2017 12:21Projects17Q2MayW233117,Q2,May,W23,31Done17Q2JunW23117,Q2,Jun,W23,01####10
1-7WDQS2RPR-BO-RDone5/31/2017 15:505/31/2017 11:15Projects17Q2MayW233117,Q2,May,W23,31Done17Q2MayW233117,Q2,May,W23,31####10
1-7ZZB48OPR-SNot Started6/5/2017 13:13 Projects Q W ,Q,,W,Planned End17Q2JunW24517,Q2,Jun,W24,05####01
1-81COCR7PR-BODone6/7/2017 7:226/6/2017 13:08Parts17Q2JunW24617,Q2,Jun,W24,06Done17Q2JunW24717,Q2,Jun,W24,07####10
1-7ZOVHY3PR-SNot Started6/6/2017 14:23 Projects Q W ,Q,,W,Planned End17Q2JunW24617,Q2,Jun,W24,06####01
1-7ZMEIZPPR-BO-RDone6/7/2017 18:006/7/2017 14:48Cyber17Q2JunW24717,Q2,Jun,W24,07Done17Q2JunW24717,Q2,Jun,W24,07####10
1-7WO3ICLPR-BO-RDone6/9/2017 19:516/9/2017 8:42Cyber17Q2JunW24917,Q2,Jun,W24,09Done17Q2JunW24917,Q2,Jun,W24,09####10
1-7X9CS3BPRDone5/23/2017 18:306/9/2017 7:44Contracts17Q2JunW24917,Q2,Jun,W24,09Done17Q2MayW222317,Q2,May,W22,23####10
1-81LSHQFPR-BO-RDone6/12/2017 5:006/9/2017 15:49Spot17Q2JunW24917,Q2,Jun,W24,09Done17Q2JunW251217,Q2,Jun,W25,12####10
1-81N760VPR-BO-RDone6/14/2017 19:376/13/2017 14:12Projects17Q2JunW251317,Q2,Jun,W25,13Done17Q2JunW251417,Q2,Jun,W25,14####10
1-81PFI12PR-SNot Started6/14/2017 14:37 Projects Q W ,Q,,W,Planned End17Q2JunW251417,Q2,Jun,W25,14####01
1-7ZSNGF7PR-BO-RDone6/14/2017 20:006/14/2017 19:47Projects17Q2JunW251417,Q2,Jun,W25,14Done17Q2JunW251417,Q2,Jun,W25,14####10
1-81PLHS2PR-BO-RDone6/30/2017 13:276/15/2017 8:16Projects17Q2JunW251517,Q2,Jun,W25,15Done17Q2JunW273017,Q2,Jun,W27,30####10
1-828R15UPR-BO-RDone6/16/2017 16:256/16/2017 15:03Spot17Q2JunW251617,Q2,Jun,W25,16Done17Q2JunW251617,Q2,Jun,W25,16####10
1-82K6YQ8PR-BO-RDone6/21/2017 8:476/16/2017 8:48Parts17Q2JunW251617,Q2,Jun,W25,16Done17Q2JunW262117,Q2,Jun,W26,21####10
1-829NE1APR-BO-RDone6/19/2017 11:006/16/2017 16:55Projects17Q2JunW251617,Q2,Jun,W25,16Done17Q2JunW261917,Q2,Jun,W26,19####10
1-7YEG3L1PR-BO-RDone6/23/2017 14:156/19/2017 7:51Projects17Q2JunW261917,Q2,Jun,W26,19Done17Q2JunW262317,Q2,Jun,W26,23####10
1-831RNZDPR-BO-RDone6/27/2017 14:126/20/2017 14:30Cyber17Q2JunW262017,Q2,Jun,W26,20Done17Q2JunW272717,Q2,Jun,W27,27####10
1-81SUF0JPR-BO-RDone6/22/2017 23:006/21/2017 10:58Projects17Q2JunW262117,Q2,Jun,W26,21Done17Q2JunW262217,Q2,Jun,W26,22####10
1-7X7UIPLPR-BO-RDone6/23/2017 8:126/21/2017 7:05Projects17Q2JunW262117,Q2,Jun,W26,21Done17Q2JunW262317,Q2,Jun,W26,23####10
1-81CTMO5PR-BO-RDone6/23/2017 16:526/21/2017 12:48Projects17Q2JunW262117,Q2,Jun,W26,21Done17Q2JunW262317,Q2,Jun,W26,23####10
1-7Y5JW5JPR-BO-RDone6/27/2017 23:006/22/2017 14:02Projects17Q2JunW262217,Q2,Jun,W26,22Done17Q2JunW272717,Q2,Jun,W27,27####10
1-81PTICJPR-BO-RDone6/23/2017 15:086/22/2017 13:00Projects17Q2JunW262217,Q2,Jun,W26,22Done17Q2JunW262317,Q2,Jun,W26,23####10
Moderator v-yuezhe-msft
Moderator

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

@RaduOrzata,

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.
RaduOrzata Frequent Visitor
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 v-yuezhe-msft
Moderator

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

@RaduOrzata,

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.