Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

Cumulative count measure

roopesh_1-1599159226881.png

 

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

Patient Count column is 

 

count(table[patienid])

 

 

1 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))
    )
)
Community Support Team _ Dina Ye
If this post helps, then please consider Accept it as the solution to help the other members find it more
quickly.

View solution in original post

7 REPLIES 7
Ashish_Mathur
Super User
Super User

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.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
Anonymous
Not applicable

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 

roopesh_0-1599186368409.png

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?

 

 

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))
    )
)
Community Support Team _ Dina Ye
If this post helps, then please consider Accept it as the solution to help the other members find it more
quickly.
Greg_Deckler
Super User
Super User

@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.


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...
Anonymous
Not applicable

roopesh_0-1599165760387.png

 

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
Not applicable

@amitchandak

 

Is there any solution for this?

 

Anonymous
Not applicable

roopesh_0-1599160602646.png

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)
	)
)

 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.