March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early bird discount ends December 31.
Register NowBe one of the first to start using Fabric Databases. View on-demand sessions with database experts and the Microsoft product team to learn just how easy it is to get started. Watch now
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)
)
)
Solved! Go to Solution.
Okay, I got it to work by filtering other years and then using this:
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.
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:
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?
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!
Your insights matter. That’s why we created a quick survey to learn about your experience finding answers to technical questions.
Arun Ulag shares exciting details about the Microsoft Fabric Conference 2025, which will be held in Las Vegas, NV.
User | Count |
---|---|
124 | |
87 | |
85 | |
70 | |
51 |
User | Count |
---|---|
205 | |
153 | |
97 | |
79 | |
69 |