Forum Discussion

Socrates's avatar
Socrates
Frequent Visitor
6 years ago
Solved

Cumulative Count for Inactive Relationship

I'm trying to get a running count of active projects in each month. I've managed to do two measures that calculate the cumulative number of projects created, the same for projects closed, and then take one from the other.

 

Data is set out like:

 

MainCodeTypeCreatedDateDoneDate
1101/01/201825/01/2018
2105/01/2018 
3103/02/201805/02/2018
4104/02/2018 
5120/02/201810/06/2018
6103/03/2018 

 

So the running active count would be:

Jan: 1

Feb: 3 (as the 20/02 project didn't close until June)

Mar: 4

 

(NB: Active relationship between [CreatedDate] and [Calendar Date] from my Date Table, inactive join between [DoneDate] and [Calendar Date]. Some [Types] are also "2" or "3" which I don't want to count)

 

 

_ActiveCasesInMonth = 

VAR AllProjs =  CALCULATE(DISTINCTCOUNT(MainData[MainCode]),MainData[Type]="1",FILTER ( ALL( DateTable ), DateTable[Calendar Date] <= MAX ( DateTable[Calendar Date] ) ))

VAR ClosedProjs = CALCULATE(DISTINCTCOUNT(MainData[MainCode]),MainData[Type]="1",USERELATIONSHIP(DateTable[Calendar Date],MainData[DoneDate]),FILTER ( ALLSELECTED( DateTable ), DateTable[Calendar Date] <= MAX ( DateTable[Calendar Date] ) ))

Return

AllProjs-ClosedProjs

 

 

Problem is, I'm doing a running count from 01/01/2018 (as some projects from then are still not closed), but I currently only want to display the current financial year (01/04/2019 to 31/03/2020) in a visual - when I filter the bar chart to this FY (using a MonthYear column from the Date Table) the running total of 'ClosedProjs' is changing along with it.

 

I'm guessing it's my use of ALLSELECTED in that's allowing the filters to have an effect, but using ALL gives me a wrong total to begin with.

 

If someone can point me in the right direction that would be great.

 

Thank you

 

  • Right, I went away and had a cup of tea and, of course, that's all I needed to see the solution:

     

    VAR ClosedProjs = CALCULATE(DISTINCTCOUNT(MainData[MainCode]),MainData[Type]="1",USERELATIONSHIP(DateTable[Calendar Date],MainData[DoneDate]),Filter(All(DateTable),DateTable[Calendar Date] <=Max(DateTable[Calendar Date])),MainData[DoneDate])

     

    Went with the ALL function, but remembed to use [DoneDate] as a filter in CALCULATE.

2 Replies

  • Socrates's avatar
    Socrates
    Frequent Visitor

    Right, I went away and had a cup of tea and, of course, that's all I needed to see the solution:

     

    VAR ClosedProjs = CALCULATE(DISTINCTCOUNT(MainData[MainCode]),MainData[Type]="1",USERELATIONSHIP(DateTable[Calendar Date],MainData[DoneDate]),Filter(All(DateTable),DateTable[Calendar Date] <=Max(DateTable[Calendar Date])),MainData[DoneDate])

     

    Went with the ALL function, but remembed to use [DoneDate] as a filter in CALCULATE.

    • garyvz's avatar
      garyvz
      New Member

      Been trying to fix this all day, and this was the answer - thank you so much!