1

I have a Project fact table which has a start date and latest activity date. One of the questions I am trying to answer is for each year how many active projects do i have?

So lets says ProjectA started in 2012 and is active in 2016. ProjectB started in 2011 and last activity was 2015.

So, the number of active projects by year needs to be shown as

2011 - 1
2012 - 2
2013 - 2
2014 - 2
2015 - 2
2016 - 1

I am confused how can i come up with the design or MDX and DAX to have this calculation done.

I have not run into a situation like this before so i am confused how to handle it.

I would really appreciate if someone can guide me. If any more detail is needed please let me know.

GregGalloway
  • 11,355
  • 3
  • 16
  • 47
AM96
  • 43
  • 7

1 Answers1

2

If you use dax with tabula o power pivot, for example i created a table with the value of year:

enter image description here

after that i have created this calculated column:

=calculate(COUNTA(Tabella1[project]),filter(Tabella1,and([Year]>=year(Tabella1[star date]),[Year]<=year(Tabella1[end date]))))

THe result is this :

enter image description here

THe formula count every project where selected year is between start and end date.

nicolò grando
  • 397
  • 1
  • 9
  • I tried it but it did not work. Using this logic i always get the yesrs till the last year when it is active in. I am using it in tabular model. I have used table which are joined to date and also a table which has no relationship with Date Table. I have used Related function and also the USERRELATIONSHIP function. I am at a point where i am struck and have spent hours on trying it to figure out. Any help would be appreciated. – AM96 Mar 07 '16 at 19:56
  • Can you show the model and the relation between table that you use to create measure? – nicolò grando Mar 07 '16 at 19:59
  • For some reason i am having issue uploading the project. Will try it again in some time. Thanks for your help. – AM96 Mar 08 '16 at 02:32