Cumulative Count for Inactive Relationship

Socrates — Mon, 09 Mar 2020 15:06:18 GMT

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:

MainCode	Type	CreatedDate	DoneDate
1	1	01/01/2018	25/01/2018
2	1	05/01/2018
3	1	03/02/2018	05/02/2018
4	1	04/02/2018
5	1	20/02/2018	10/06/2018
6	1	03/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

Re: Cumulative Count for Inactive Relationship

Socrates — Mon, 09 Mar 2020 16:21:37 GMT

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.

Re: Cumulative Count for Inactive Relationship

garyvz — Wed, 15 Jun 2022 04:10:20 GMT

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

topic Cumulative Count for Inactive Relationship in DAX Commands and Tips

Cumulative Count for Inactive Relationship

Re: Cumulative Count for Inactive Relationship

Re: Cumulative Count for Inactive Relationship