Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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)
)
)
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
115 | |
99 | |
86 | |
70 | |
62 |
User | Count |
---|---|
151 | |
120 | |
103 | |
87 | |
68 |