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))
    )
)
Ashish_Mathur's avatar
Ashish_Mathur
Icon for Super User rankSuper 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
      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))
    )
)