Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
I looking to creating a cumulative measure on the patient count.
Patient Count column is
count(table[patienid])
Solved! Go to 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))
)
)
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.
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?
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 - 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.
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
)
)
)
@amitchandak
Is there any solution for this?
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)
)
)
User | Count |
---|---|
130 | |
108 | |
101 | |
67 | |
62 |
User | Count |
---|---|
137 | |
115 | |
102 | |
71 | |
61 |