Forum Discussion
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 ta...
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.
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.
Been trying to fix this all day, and this was the answer - thank you so much!
