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))
    )
)
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
Icon for Community Support rankCommunity 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))
    )
)