Cumulative count measure

[Anonymous]

I looking to creating a cumulative measure on the patient count.

Patient Count column is 

count(table[patienid])

[Ashish_Mathur]

Hi,

Drag Year and Month from the Calendar Table.  In your visual, sort furst by Year and then by month.  Try this measure

=calculate([patient count],datesbetween(calendar[date],minx(all(calendar),calendar[date]),max(calendar[date])))

Hope this helps.

[Anonymous]

This what i have done so far

RunningTotal1 = 
VAR __year = MAX(TrendingTable[Date_Start_Statusdate].[Year])
RETURN
CALCULATE(
    count(TrendingTable[vw_AccrualsList.patientId]),
    FILTER(allselected(TrendingTable), TrendingTable[Date_Start_Statusdate] <= MAX(TrendingTable[Date_Start_Statusdate])
    )
)

gives me 

The problem is after December 2018 it has to rest and in January 2019 it has to start from 99 and cumulative and so on.

How do I make it rest after the end of every year?

[v-diye-msft · Accepted solution]

Hi @Anonymous 

Try this one:

RunningTotal1 = 
VAR __year = MAX(TrendingTable[Date_Start_Statusdate].[Year])
RETURN
CALCULATE(
    count(TrendingTable[vw_AccrualsList.patientId]),
    FILTER(allselected(TrendingTable), TrendingTable[Date_Start_Statusdate] <= MAX(TrendingTable[Date_Start_Statusdate]),values(TrendingTable(Year))
    )
)

[Greg_Deckler]

@Anonymous - Can you just use the Running Total Quick Measure that is built into the Desktop? In Fields, click the ellipses next to Patient Count and choose Quick Measure and then Running Total.

[Anonymous]

I added month to and still did not work

RunningTotal = 
CALCULATE(
	COUNTA('TrendingTable'[vw_AccrualsList.patientId]),
	FILTER(
		CALCULATETABLE(
			SUMMARIZE(
				'TrendingTable',
				'TrendingTable'[CurrentStatusDate].[MonthNo],
				'TrendingTable'[CurrentStatusDate].[Month]
			),
			ALLSELECTED('TrendingTable')
		),
		ISONORAFTER(
			'TrendingTable'[CurrentStatusDate].[MonthNo], MAX('TrendingTable'[CurrentStatusDate].[MonthNo]), DESC,
			'TrendingTable'[CurrentStatusDate].[Month], MAX('TrendingTable'[CurrentStatusDate].[Month]), DESC
		)
	)
)

[Anonymous]

@amitchandak

Is there any solution for this?

[Anonymous]

It gives me the running total on years I want it on month jan 2018,feb 2018 etc

RunningTotal = 
CALCULATE(
	COUNTA('TrendingTable'[vw_AccrualsList.patientId]),
	FILTER(
		ALLSELECTED('TrendingTable'[vw_AccrualsList.StatusDate].[Year]),
		ISONORAFTER('TrendingTable'[vw_AccrualsList.StatusDate].[Year], MAX('TrendingTable'[vw_AccrualsList.StatusDate].[Year]), DESC)
	)
)