Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started
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?
Check out the September 2024 Power BI update to learn about new features.
Learn from experts, get hands-on experience, and win awesome prizes.
User | Count |
---|---|
113 | |
109 | |
100 | |
39 | |
31 |