Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Cumulative count measure

 

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

Patient Count column is 

 

count(table[patienid])

 

 

Need Help
  • v-diye-msft's avatar
    v-diye-msft
    6 years ago

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

7 Replies

  • Greg_Deckler's avatar
    Greg_Deckler
    Community Champion
    6 years ago

    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's avatar
      Anonymous
      Not applicable
      6 years ago

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

       

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

       

      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's avatar
        Anonymous
        Not applicable
        6 years ago

        @amitchandak

         

        Is there any solution for this?

         

  • Ashish_Mathur's avatar
    Ashish_Mathur
    Super User
    6 years ago

    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's avatar
      Anonymous
      Not applicable
      6 years ago

      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's avatar
        v-diye-msft
        Community Support
        6 years ago

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