Forum Discussion

he2mpo's avatar
he2mpo
Frequent Visitor
3 years ago
Solved

Counting rows with variance from same table

Hello all

 

I have two tables one for tasks and the second for projects. Each project has many tasks. The tasks table shown below

it has fields as id code, project id, start date and finish date.

 

The second table is a simple one having project id and project name

It is important to note that the connection between the tasks is the task code as the ids are not the same between different project

 

I have created two slicers for users to select the current project and the baseline project. Then created a set of measures to calculate the start and finish dates based on the selected values in both slicers.

 

 

Now what I am trying to achieve is to count the number of activities where the variance is less than 0.

I tried creating a virtual table that will have the start date from the baseline and the start date from the current forcast and then used COUNTX with filter. Unfortunatley this did not work as it returned the total count of activites.

 

 

 

cnt = 
var comb = 
ADDCOLUMNS(TASK, 
    "task_code", CALCULATE(DISTINCT(TASK[task code]), FILTER(TASK, TASK[proj] = LASTNONBLANK('Current'[proj id],1))) ,
    "blStart", CALCULATE(Min(TASK[Start]), filter(TASK, TASK[proj] = LASTNONBLANK(Baselines[proj id],1))),
    "curStart", CALCULATE(min(TASK[Start]), FILTER(TASK, TASK[proj] = LASTNONBLANK('Current'[proj id],1)))
)
RETURN
COUNTX(Filter(comb, ([blStart] - [curStart])*1.0 < 0 ), [task code])

 

 

 

I appreciate if you could help in this issue. 

The link to pbix and data file is here

Comparison Example

 

Many thanks

 

2 Replies