Forum Discussion
help with drill through with cumulative total
Okay, I got it to work by filtering other years and then using this:
CALCULATE(COUNTA('Table 1'[Date of Incident]),FILTER(ALL('Table 1'),ISONORAFTER('Table 1'[Date of Incident], MAX('Table 1'[Date of Incident]), DESC)))
Resident RockstarTry this:
Cumulative Incidents =
CALCULATE(
COUNTA('Table1'[Date Incident]),
FILTER(
ALL('Table 1'[Date Incident]),
'Table 1'[Date Incident].[Year] = SELECTEDVALUE('Table 1'[Date Incident].[Year]) &&
ISONORAFTER('Table1'[Date Incident], MAX('Table1'[Date Incident]), DESC)
)
)ALLSELECTED returns all data currently being shown in your visual. You want all data for the year, so I changed it to ALL and then added an extra condition to make sure you're only using data from the current year.
EDIT: Forgot right parens. Added in red.
It won't let me add the ".[YEAR]", it's grayed out when I try to type the formula suggested and it gives a message that a single value for variation [YEAR] for the column cannot be determined. I do have more than one year worth of dates in that column.
Okay, I got it to work by filtering other years and then using this:
CALCULATE(COUNTA('Table 1'[Date of Incident]),FILTER(ALL('Table 1'),ISONORAFTER('Table 1'[Date of Incident], MAX('Table 1'[Date of Incident]), DESC)))- Cmcmahan
I'm glad you got it!
My original solution missed a right parens, so I added it back.
I'm curious which .[Year] it was giving you a hassle for. Was it the first or 2nd one?