help with drill through with cumulative total

[toniacheung]

I have a visual that shows the number of incidents over the year, with a line showing the accumulation for the year.  When I drill through to show the data by week (or month, or year), however, the line showing the cumulation doesn't work right.  It just shows the total for each time period rather than for the whole year.   How can I still get it to show the cumulative amount when I drill through?

The DAX formula for the line is: 

Cumulative Incidents =

CALCULATE(

COUNTA('Table1'[Date Incident]),

FILTER(

        ALLSELECTED('Table 1'[Date Incident]),

        ISONORAFTER('Table1'[Date Incident], MAX('Table1'[Date Incident]), DESC)

    )

)

correctincorrect when I drill through

[Cmcmahan]

Try 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.

[toniacheung]

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. 

[toniacheung · Accepted solution]

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?