Skip to main content
cancel
Turn on suggestions
Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.
Showing results for 
Search instead for 
Did you mean: 

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

Reply
RaduOrzata
Frequent Visitor

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

‎07-24-2017 07:19 AM

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

Message 1 of 6
1,822 Views
0
1 ACCEPTED SOLUTION
v-yuezhe-msft
Employee
Employee
In response to RaduOrzata

‎07-25-2017 03:06 AM

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

View solution in original post

Message 6 of 6
2,109 Views
0
5 REPLIES 5
Greg_Deckler
Super User
Super User

‎07-24-2017 08:07 AM

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.


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!: The Definitive Guide to Power Query (M)

DAX is easy, CALCULATE makes DAX hard...
Message 2 of 6
1,816 Views
0
RaduOrzata
Frequent Visitor
In response to Greg_Deckler

‎07-25-2017 12:42 AM

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
Message 3 of 6
1,803 Views
0
v-yuezhe-msft
Employee
Employee
In response to RaduOrzata

‎07-25-2017 02:05 AM

@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.
Message 4 of 6
1,795 Views
0
RaduOrzata
Frequent Visitor
In response to v-yuezhe-msft

‎07-25-2017 02:36 AM

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.

Message 5 of 6
1,792 Views
0
v-yuezhe-msft
Employee
Employee
In response to RaduOrzata

‎07-25-2017 03:06 AM

@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.
Message 6 of 6
2,110 Views
0

Helpful resources

Announcements
Microsoft Fabric Learn Together

Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

April Fabric Community Update

Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.

View All